CODEWITHSUNDEEP
vbaexcel
Sean Kelly
Sean Kelly

Reputation: 183

Simple VBA to Select a Range from First .Find Value?

I want to locate a value within column "A" then select from that address to the first blank row encountered. Here is what I have, but Set f isn't working. My Set c works and returns $A$32, which is the correct address. What am I missing?

With Workbooks("MyBook").Sheets("Mysheet")
    Set c = .Range("$A:$A").Find("Red Car").Offset(2, 0)
    Set f = .Range(c.Address & Range("A").End(xlDown).Row)
End With

Upvotes: 0

Views: 341

Answers (2)

Busse
Busse

Reputation: 863

Removed Code

You are just trying to assign Range($A$32 & LastRow). Without adding the : (colon) and column letter (A), it cannot properly assign the range to F. Try this code and see if it works now.

Upvotes: 1

SJR
SJR

Reputation: 23081

Try this, and check first that the search term is found

With Workbooks("MyBook").Sheets("Mysheet")
    Set c = .Range("$A:$A").Find("Red Car")
    If Not c Is Nothing Then Set f = Range(c.Offset(2, 0), c.Offset(2, 0).End(xlDown))
End With

Upvotes: 2

Related Questions