Object required

[lynxbci]

Hi,
I am trying to select a column range by first finding which column has the title I require (in this case "UseThis")
Then I want to select the range from that address (f.address) and offset it by a number of rows (in this case I am using 100)
The code works and msgBox shows $H$1 as expected from my data, as "UseThis" is in column H.
I then want to create a range $H$1:$H$100 for use later in a formula.

I get the 'Object Required Error (424)' when I try to set indexRange property.. what have I done wrong?

Sub findIndex()

Dim indexRange As Range

'FINDS THE COLUMN TO USE AS A INDEX
With ActiveSheet.UsedRange

    Set f = .Find("UseThis", LookIn:=xlValues)
    
    If Not f Is Nothing Then
        MsgBox f.Address
        indexRange = f.Address.Offset(100, 0)
       
        
    End If

End With

End Sub

 

[CountTepes]

.address returns the string value of the address.

indexRange=f.offset(100,0) should do you.

 

[Fluff]

How about

Set indexRange = f.Resize(100)

 

[lynxbci]

How about

Set indexRange = f.Resize(100)

Thanks, works a treat

 

[Fluff]

You're welcome & thanks for the feedback