CODEWITHSUNDEEP
vbaexcel
user2930203
user2930203

Reputation: 1

copying and pasting into another cell

we are trying to go through a range of cells looking for a particular string. In one case the string is accessibility but the whole string is accessibility3.2. Then we want to paste the string to a cell on another sheet.

then the next time through the loop it could be accessibility5.8

here is some code we have written:

srtDate = ThisWorkbook.Sheets("Website Quality").Cells(9, 3).Value

rng = ThisWorkbook.Sheets("Sheet2").Column(1).Find(What:=Accessibility, _
                    LookIn:=x1Values, LookAt:=x1Whole, MatchCase:=True)

srtDate.Value = rng.Value

any help would be great Thanks

Upvotes: 0

Views: 195

Answers (2)

DaveU
DaveU

Reputation: 1082

Maybe this is what you want (I'm assuming "Accesibility" is a literal string).

Change

rng = ThisWorkbook.Sheets("Sheet2").Column(1).Find(What:=Accessibility, _ LookIn:=x1Values, LookAt:=x1Whole, MatchCase:=True)

to

Set rng = ThisWorkbook.Sheets("Sheet2").Columns(1).Find(What:="Accessibility", _
                LookIn:=x1Values, LookAt:=x1Part, MatchCase:=True)

Upvotes: 1

Mark Fitzgerald
Mark Fitzgerald

Reputation: 3068

Your .Find is obviously the Range.Find method because it contains arguments that aren't present in the WorksheetFunction.Find method.

The Range.Find method requires an expression that evaluates to a Range Object.

ThisWorkbook.Sheets("Sheet2").column(1)

doesn't evaluate to a range. You can test this by entering in the Immediate pane 

ThisWorkbook.Sheets("Sheet2").column(1).select

which will return an error 438

Object doesn't support this method or property

whereas 

ThisWorkbook.Sheets("Sheet2").Range("A:A").select

works just fine in the Immediate pane so replace ThisWorkbook.Sheets("Sheet2").Column(1) with ThisWorkbook.Sheets("Sheet2").Range("A:A")

The return from a Range.Find method is a range so the variable that takes the return should be defined as a range

Dim rng as Range

If you don't have Option Explicit at the top of your module, all variables are assumed by VBA to be of Variant Type but the Variant Type is not interchangeable with Object Types.

If you set your VBA IDE to always "Require variable declaration" via Tools > Options > Editor Tab Option Explicit will be added to every module in your projects and you will not suffer from incorrect variable calls.

Now Objects can't be assigned by using ObjectName = .... They must be Set using Set ObjectName = ... and the return must be of the same type as the object being set.

If you Dim rng as Range then the method on the right side of the Set rng = ... must return an object of the same type or an error will occur.

Your What:=Accessibility, is trying to find the contents of a variable named Accessibility which probably doesn't exist. Find the string "Accesibility" will work even if it's embedded within other characters if you LookAt:=xlPart.

To summarise:

Define your object Dim rng as Range

and change your line

rng = ThisWorkbook.Sheets("Sheet2").Column(1).Find(What:=Accessibility, _
                LookIn:=x1Values, LookAt:=x1Whole, MatchCase:=True)

to:

With ThisWorkbook.Sheets("Sheet2").Range("A:A")
    Set rng = .Find(what:="Accessibility, LookIn:=xlValues, LookAt:=xlPart)   
End With

which should leave you with a rng.value that you can use.

Upvotes: 0

Related Questions