find function not working on cells which reference other cells

[switters_aka]

can anyone help me with the following which is probably really simple but it's driving me mental. I am using the find function to establish the row number in a spreadsheet for an edit process.

wks.Range("B:B").find("something").Row

the cells in Range("B:B") are linked to another worksheet. If I copy paste values on the cells in Range("B:B") the code above works a treat. Any ideas?

 

[VoG]

Try

wks.Range("B:B").Find(what:="something", LookIn:=xlFormulas, lookat:=xlPart).Row

 

[switters_aka]

Thanks VoG. Tried what you suggested but that produced an error too. This is most strange and most frustrating. I have included the complete code below. Is there an issue with the space in the string. I tried changing that to "Installment" & Space(1) & "1" but it didn't make any difference. I am totally stumped.

Sub findinstallment()
Dim zrow As Integer
Dim wks As Worksheet
Set wks = Application.Worksheets("ROI")
zrow = wks.Range("B:B").Find(what:="Installment 1", LookIn:=xlFormulas).Row
MsgBox zrow
End Sub

 

[VoG]

I would check for leading and trailing spaces or maybe extra spaces between Installment and 1.

I would code it like this

Sub findinstallment()
Dim zrow As Integer, Found As Range
Dim wks As Worksheet
Set wks = Application.Worksheets("ROI")
Set Found = wks.Range("B:B").Find(what:="Installment 1", LookIn:=xlFormulas)
If Found Is Nothing Then
    MsgBox "Not found"
Else
    zrow = Found.Row
    MsgBox zrow
End If
End Sub

 

[GTO]

Hi there,

You make mention in the thread title and post one that the cells refer to another sheet. Do we know for sure what the value is (that is, the word(s)/number/etc) that will appear in the sheet we are searching? If yes, I believe you want to change to xlValues.

Mark