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

 

[Norie]

Jenny

What is that code meant to do?

 

[Rick Rothstein]

Type Range.Find into the Immediate window and then press F1 to bring up the Help File on the Find function I think you are asking about. Note that for bring up the Help File, you do not have to specify an actual valid range reference as you would need to do when actually using it in code.

 

[zookeepertx]

Jenny

What is that code meant to do?

The ENTIRE code is to put a thin border throughout a whole set of data, then find where there's more than 1 row with data in a given column and put a thicker border around that section. It'll hopefully make it easier for the user to differentiate between the sections. I have code that works if the data starts in A1, and am trying to adapt it to data elsewhere in the worksheet.

I really don't know what to think about the error message that comes up on that line either. "Run-time error '91': Object variable or With block variable not set"

 

[Joe4]

Can you post your entire block of code, so we can see what d and clm are set to?

 

[Norie]

The error message means that the Find method isn't finding what you are telling it to look for.

 

[zookeepertx]

Type Range.Find into the Immediate window and then press F1 to bring up the Help File on the Find function I think you are asking about. Note that for bring up the Help File, you do not have to specify an actual valid range reference as you would need to do when actually using it in code.

Awesome; exactly what I needed to know! I've written that down because I'm SURE I'll need it again, LOL! Thank you!

So, the arguments for "FIND" are: (What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat). The last thing in this code is a number, but all I can find from googling is that all of the other arguments are represented by "words". The 1 seems to be in the place for SearchFormat, but no place (that I've found) tells me what the options are for that argument. (Sometimes I'm SO dense! ☹)

Jenny

 

[Rick Rothstein]

The value for the SearchFormat argument is True or False. In VBA, anything looking for a Boolean value will coerce a zero to False and any non-zero value to True. Some people use 1 (simple number to type) in place of True for arguments looking for a Boolean which appears to be the case here.

Also, as an aside, you can type any keyword into the Immediate window and get the Help File for it by pressing F1. For things like Find where is can have different usages, you would type it in context (like I did with Range.Find rather than just typing Find by itself).

 

[zookeepertx]

Can you post your entire block of code, so we can see what d and clm are set to?

Sure.

Sub OutlineMatchingData2()
'Jenny

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
    .DisplayAlerts = False
End With

Dim clm As Range, Rng As Range, lc As Long
Dim a As Long, b As Long, d As Long, i As Long, lr As Long, LR2 As Long
lc = Cells(1, Columns.count).End(xlToLeft).Column

'Lets you select which column you want to group your data by
Set clm = Application.InputBox _
(Prompt:="Please select any cell in the column containing the distinguishing data", _
Title:="Selection required", Type:=8)
Last = Cells(Rows.count, clm.Column).End(xlUp).Row

'Find last column and row of new table of data
lr = Cells(Rows.count, 31).End(xlUp).Row
LR2 = Cells(Rows.count, 32).End(xlUp).Row
lc = Cells(LR2, Columns.count).End(xlToLeft).Column

d = lr + 2
Application.ScreenUpdating = False
Range(Cells(lr + 2, "AF"), Cells(LR2, lc)).Select

'Determines the parameters of each matching set of data
Do Until b = LR2
  
   b = Columns(clm.Column).Find(Cells(d, clm.Column).Value, , , , , 1).Row
   a = Columns(clm.Column).Find(Cells(d, clm.Column).Value, , , 1).Row

'Puts a heavy border around the entire group of matching data
With Range(Cells(a, 1), Cells(b, lc))

    .BorderAround LineStyle:=xlContinuous, ColorIndex:=xlAutomatic, Weight:=xlMedium

'Puts thin border on the interior of the entire group of matching data
With .Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .ColorIndex = xlAutomatic
    .Weight = xlThin
End With

With .Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .ColorIndex = xlAutomatic
    .Weight = xlHairline
End With

End With

d = b + 1

Loop
  
With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
    .DisplayAlerts = True
End With

End Sub

There's a set of data that always starts in row 12 but there can be any number of rows or columns. I have a macro that takes that data, pulls the info we need and pastes it 2 rows below the last row of that and it will always start in column AF. That table could also be any number of rows and any number of columns.
SO, lr is the last row of the original data, LR2 is the last row of the newly created table of data, and lc is the last column of data in last row of the newly created table of data.

I'm attaching an example, including the row and column numbers/letters for reference.

Thanks!

 

[zookeepertx]

Perfect! thanks