[SOLVED]Dynamic range selection

[Jape]

Ok i can't seem to figure this one out, im trying to do a .find in a dynamic range using the following statement:

Set rng4 = Sheets("TempC").Range("AY2", Range("AY65000").End(xlUp))

For some reason it gives me a '1004' error, but if i do it trough a .select it works ok . Am i overlooking something ?, at first i was thinking of doing it this way:

Sheets("TempC").Range("AY2", Range("AY65000").End(xlUp)).Find(rng2.Offset(0, 1).Value, LookIn:=xlValues, lookat:=xlWhole)

Also gives me a '1004' error, its driving me crazy, why is this not possible, should i do it trough a select statement or is there an other way around, also tried to do it trough an cells(x,x).end(xlup) but also doesnt work. Cant seem to figure it out, can someone point me to my mistake ?

Thanks in advance, Jape,

 

[Norie]

Jape

As far as I can see, and I've only had a quick look, the syntax of your code is wrong.

What are you actually trying to do?

 

[Jape]

Hi, Norie

Im trying to do a .find in a column with only the usedrange of that column (so .usedrange is not an option), i have a list with numbers populated from row 2 to, lets say, row 450, i want to do a find in only these rows in one specific column.

Ive copied the same line from the VBA help file

Range("B4", Range("B4").End(xlToRight)).Select

but when i want to use that in my example it gives me an error, if i copy my example and simply put the next line in the intermediate box it works fine;

Sheets("TempD").Range(Range("AW2"), Range("AW6500").End(xlUp)).select

Thats the thing that confuses me, i dont see why i cant use it in my original example.

Hope ive been clear, been messing to long with this one

 

[Jape]

Btw, my next attempt also did not work, by using the range(cells(x,y),cells(x,y) method.

Clmn = Sheets("TempC").Range("AY65000").End(xlUp).Column
Set rng2 = Sheets("TempC").Range(Cells(2, 51), Cells(Clmn, 51)).Find(Right(rng.Value, Len(rng.Value) - InStr(1, rng.Value, "|")), LookIn:=xlValues, lookat:=xlWhole)

 

[pgc01]

Hi Jape

I haven't looked yet at your whole problem, but about the statement in your first post, you have to activate the worksheet before the set statement. Try:

Sheets("TempC").Activate
Set rng4 = Range("AY2", Range("AY65000").End(xlUp))

The example you took from the help file was working with the active sheet.

Hope this helps
PGC

 

[Jape]

Hi pgc,

your code does work without errors, but for some reason it looks like the sheet.activate has no influence on the find method beceause it doesnt find what im looking for even though i doublechecked the range(probably its searching the range on the wrong sheet). Putting the sheets(x) in front of the range in the set command works fine as long as i dont use a dynamic range. Its all very unlogical to me somehow.

Jape,

 

[Norie]

Don't activate the sheet, reference it.

With Sheets("TempC")
Clmn = .Range("AY65000").End(xlUp).Column

Set rng2 = .Range(.Cells(2, 51), .Cells(Clmn, 51)).Find(Right(rng.Value, Len(rng.Value) - InStr(1, rng.Value, "|")), LookIn:=xlValues, lookat:=xlWhole)

By the way this

Clmn = .Range("AY65000").End(xlUp).Column

doesn't make sense really.

Couldn't you just use this?

Clmn = .Range("AY65000").Column

 

[Jape]

Thx Norie, it works perfect indeed

about the column part, you're right, already noticed that too, should have been the row number i wanted, so changed that too. Sometimes i just type too fast without thinking properly .

Again thanks,

Jape,