Try this code. Instead of Range you can also use Rows or columns.
Set searchrange = Range("A1:D7")
or
Set searchrange = Columns("A:A")
or
Set searchrange = Rows(5)



Set searchrange = Range("A1:D7")
searchstring = "mystring"
Set c = searchrange.Find(what:=searchstring, LookIn:=xlValues)
If Not c Is Nothing Then
lookupdata = c.Offset(0, 3)
End If

"Damien McBain" wrote:

Hi

I have a sub which uses a For...Next loop to populate the cells in a
column.based on the contents of column A in the same row.

I presently use WorksheetFunction.VLookup to get the value from another
worksheet. Is there a faster way to achieve the same result, maybe without
calling a worksheet function? The code takes some time to run through about
400 cells. I have auto calculation turned off during execution.

TIA

Damien