PDA

View Full Version : Is it posible to use range("CR:CR").Find(value) to find a value between two numbers



k13r4n
02-15-2009, 01:59 PM
Hi all

Ive writen a pretty heavy macro that uses .Find(Value) and .FindNext(Value) to find some info in a spreadsheet and pull all ascosiated data. That all works perfectly and does exactly what i wanted but now they want it to be able to find cells that contain a Value between 2 numbers :banghead:

Ive come up with a way to do it using 'if then else' and 'select cases' etc but it requires a complete re-write of a large chunk of code.

Is it possible to use .Find(value) to do this?? if it is it's gona save me a load of hassle.

E.G.

i have a variable T and i want to find cells that have a value in the range of plus or minus 1.5 from T, IE if T = 5 i want to find everything from 3.5 to 6.5 inclusive.


Chears for any help


Kieran

mdmackillop
02-15-2009, 02:55 PM
Check this (http://www.ozgrid.com/VBA/find-between.htm) out. Any queries, let us know.

mdmackillop
02-15-2009, 03:00 PM
If your data is not too big, you could try a simpler loop

Sub FindBetween()
T = 5
Set Rng = Cells(1, 6).CurrentRegion
For Each cel In Rng
If Abs(cel - T) <= 1.5 Then cel.Interior.ColorIndex = 6
Next
End Sub

k13r4n
02-15-2009, 03:23 PM
Thanks for the reply,

that is sort of along the lines of what im trying now, it was quite nice using the .Find method as it returns a range object which i could activate and then work from there but now im goin to try your method and get the row number (column is known) and try reworking the code from there.
will let you know how i get on



cheers

kieran

mdmackillop
02-16-2009, 05:14 AM
Another useful item (http://www.thecodenet.com/articles.php?id=18)