Syntax in a long FIND formula

[zookeepertx]

Hello all!

I was recently given some VBA code and am trying to decipher it. One of the items is a FIND formula with a lot of commas at the end, which, I'm sure represent parameters for the FIND. But I can NOT figure them out! I've been googling forever and all I can come up with is that the beginning syntax is FIND(find_text, within_text, [start_num]) but nothing that tells me what the rest of the formula is doing. Can anyone help me? Here's the formula:
b = Columns(clm.Column).Find(Cells(d, clm.Column).Value, , , , , 1).Row
When the macro gets to that line it fails with the error message "Run-time error '91': Object variable or With block variable not set", which I don't know what that means, either.
* * * Please note that "clm.Column" just represents the column that the user selected earlier in the code. * * *

Thanks for any help

Jenny

 

[Joe4]

Using Find like that results in an error if it cannot find the value. One way to avoid that is instead of structuring like this:

b = Columns(clm.Column).Find(Cells(d, clm.Column).Value, , , , , 1).Row

do somethinig like this:

Dim rngB as Range
Set rngB = Columns(clm.Column).Find(Cells(d, clm.Column).Value, , , , , 1)
If rngB is Nothing Then
    MsgBox "Value not found"
Else
    b = rngB.Row
End If

 

[zookeepertx]

I'll give that a try on Monday. Time to clock out if I don't want to get in trouble.

 

[zookeepertx]

VBA Code:

Dim rngB as Range
Set rngB = Columns(clm.Column).Find(Cells(d, clm.Column).Value, , , , , 1)
If rngB is Nothing Then
    MsgBox "Value not found"
Else
    b = rngB.Row
End If

I just tried that and got "Value not found", even though I know there are more instances of the value being searched for. So, then I thought maybe both a and b needed to be coded like that and repeated it with reference to a. Still got the "Value not found" except it showed up twice in a row. (At least it was different, LOL!

I have to clock out in about 6 minutes to avoid trouble (we're not allowed to have even 1 minute over 40 hrs in a week), so I'll have to revisit this on Monday.

Thanks!

Jenny