[SOLVED]Dynamic range selection

Jape

New Member
Joined
Sep 30, 2004
Messages
47
Ok i can't seem to figure this one out, im trying to do a .find in a dynamic range using the following statement:

Code:
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 :eek: . Am i overlooking something ?, at first i was thinking of doing it this way:

Code:
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,
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 30, 2004
Messages
47
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
Code:
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;
Code:
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 :LOL:
 

Jape

New Member
Joined
Sep 30, 2004
Messages
47
Btw, my next attempt also did not work, by using the range(cells(x,y),cells(x,y) method.

Code:
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

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:

Code:
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

New Member
Joined
Sep 30, 2004
Messages
47
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
Don't activate the sheet, reference it.
Code:
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
Code:
Clmn = .Range("AY65000").End(xlUp).Column
doesn't make sense really.

Couldn't you just use this?
Code:
Clmn = .Range("AY65000").Column
 

Jape

New Member
Joined
Sep 30, 2004
Messages
47
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,
 

Forum statistics

Threads
1,141,018
Messages
5,703,756
Members
421,313
Latest member
Mooncake1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top