find function not working on cells which reference other cells

switters_aka

Board Regular
Joined
Oct 26, 2010
Messages
118
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.

Code:
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? :confused:
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

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

switters_aka

Board Regular
Joined
Oct 26, 2010
Messages
118
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. :confused:
Code:
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

Legend
Joined
Jun 19, 2002
Messages
63,650
I would check for leading and trailing spaces or maybe extra spaces between Installment and 1.

I would code it like this

Code:
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

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,861
Messages
5,627,315
Members
416,239
Latest member
Counselor85027

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top