Find in a dynamic column...
 

I know this is a stupid question and I'm sorry in advance!!!

Set ytdRng = LevelRng.Range("a1:a1000").Find(What:=prevName, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)


LevelRng is a range that points to the first cell in the column I want
to search... How do I have it search the whole column? I currently
have it searching a fixed range ("a1:a1000") and that works, but I'd
like it to just search to the bottom of the used cells in that column.
FYI, there are blank cells in the column...

Thanks,

Bill

Find in a dynamic column...
 

replace
range("a1:a1000")

with
range(levelrng.range("a1"),levelrng.range("a65536" ).end(xlup))
--
JNW


"billboe" wrote:

I know this is a stupid question and I'm sorry in advance!!!

Set ytdRng = LevelRng.Range("a1:a1000").Find(What:=prevName, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)


LevelRng is a range that points to the first cell in the column I want
to search... How do I have it search the whole column? I currently
have it searching a fixed range ("a1:a1000") and that works, but I'd
like it to just search to the bottom of the used cells in that column.
FYI, there are blank cells in the column...

Thanks,

Bill

Find in a dynamic column...
 

If levelrng is not in the first row you'll get an error using
levelrng.range("a65536"). I know the OP states he is using a fixed range of
A1:A1000, but his code indicates this range is relative to levelrng.

Just in case levelrng might not be in row 1:
Range(levelrng, Cells(Rows.Count, levelrng.Column).End(xlUp))


"JNW" wrote:

replace
range("a1:a1000")

with
range(levelrng.range("a1"),levelrng.range("a65536" ).end(xlup))
--
JNW


"billboe" wrote:

I know this is a stupid question and I'm sorry in advance!!!

Set ytdRng = LevelRng.Range("a1:a1000").Find(What:=prevName, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)


LevelRng is a range that points to the first cell in the column I want
to search... How do I have it search the whole column? I currently
have it searching a fixed range ("a1:a1000") and that works, but I'd
like it to just search to the bottom of the used cells in that column.
FYI, there are blank cells in the column...

Thanks,

Bill

Find in a dynamic column...
 

JMB wrote:
If levelrng is not in the first row you'll get an error using
levelrng.range("a65536"). I know the OP states he is using a fixed range of
A1:A1000, but his code indicates this range is relative to levelrng.

Just in case levelrng might not be in row 1:
Range(levelrng, Cells(Rows.Count, levelrng.Column).End(xlUp))


Thanks to you both for your responses! JMB was correct, levelrng was
not in the 1st row... However, I had a heck of a time to get this
running... I finally figured out that the sheet needed be activated
for it to work. At least, that is what made it work for me... Does
that make sense!?!?


Thanks again!!!

Bill

Find in a dynamic column...
 

Sorry about that. Without qualifying the worksheet, it will run on the
active sheet. I should have done that, but I assumed you were either working
w/the active sheet or you were familiar w/fully qualifying the range.


With Worksheets("Sheet1")
Set ytdRng = .Range(levelrng, .Cells(.Rows.Count, levelrng.Column).End(xlUp))
.Find(What:=prevName, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
End With

or use the Parent property of levelrng
With levelrng.Parent
Set ytdRng = .Range(levelrng, .Cells(.Rows.Count, levelrng.Column).End(xlUp))
.Find(What:=prevName, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
End With


"billboe" wrote:

JMB wrote:
If levelrng is not in the first row you'll get an error using
levelrng.range("a65536"). I know the OP states he is using a fixed range of
A1:A1000, but his code indicates this range is relative to levelrng.

Just in case levelrng might not be in row 1:
Range(levelrng, Cells(Rows.Count, levelrng.Column).End(xlUp))


Thanks to you both for your responses! JMB was correct, levelrng was
not in the 1st row... However, I had a heck of a time to get this
running... I finally figured out that the sheet needed be activated
for it to work. At least, that is what made it work for me... Does
that make sense!?!?


Thanks again!!!

Bill