copying and pasting into another cell

Question

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

Answer 1

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)

Answer 2

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.