Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Find | Excel Programming | |||
Help with copying dynamic column selected based on remote cell value and dynamic formula fill | Charts and Charting in Excel | |||
Help with copying dynamic column selected based on remote cell value and dynamic formula fill | Excel Programming | |||
trying to find the smallest with dynamic column changes | Excel Discussion (Misc queries) | |||
Dynamic Find and Replace String Within Functions Based on Column Values | Excel Programming |