Using .Find

[JSA715]

According to the help in the excel IDE, .Find returns a range object.

So, why instead of getting a range, it appears to return the value I am searching for?

Dim rFound as Range
Dim iBr as Integer
iBr = 3
          With WB.Sheets("GL Transaction Totals - MTD and YTD")
          Set rFound = .Range("C6:C20000").Find(iBr, LookIn:=xlValues, lookat:=xlWhole)

rFound = 3??
iBr is an interger

 

[Colin Legg]

It does return a range (provided it finds a match). 3 will be the value in the cell it's found. Note, you are using the Set keyword and you declared rFound as a Range.

 

[JSA715]

Hey Colin,

Thanks for the reply.

Well I declared it as a range and used set because I want to the reference the range in another expression down the road. However, "3" isnt a range. How would I set the rFound to the address of the expression?

 

[Colin Legg]

Hi,

3 is value in the cell that was found.

To get the address:

Dim rFound as Range
Dim iBr as Integer
iBr = 3
          With WB.Sheets("GL Transaction Totals - MTD and YTD")
          Set rFound = .Range("C6:C20000").Find(iBr, LookIn:=xlValues, lookat:=xlWhole)
          If Not rFound Is Nothing Then MsgBox rFound.Address

Hope that helps...