Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
what is the best way to find a value in a sheet in VBA?
..Find(x, LookIn:=xlValues)
Is this the only way? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
what is the best way to find a value in a sheet in VBA?
It is the most general way if you don't want to loop.
You can also use the built in lookup, vlookup, match type functions. -- Regards, Tom Ogilvy "serdar" wrote in message ... .Find(x, LookIn:=xlValues) Is this the only way? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
what is the best way to find a value in a sheet in VBA?
if i look up to some columns with:
Columns("A:D").Find(x, LookIn:=xlValues) ,does this looks "all" rows in columns a to d, or just the formatted ones? If first is true, does this takes too much time than looking up by giving the actual workspace as a range(not a good solution for me)? thanks. "Tom Ogilvy" , haber iletisinde şunları ... It is the most general way if you don't want to loop. You can also use the built in lookup, vlookup, match type functions. -- Regards, Tom Ogilvy "serdar" wrote in message ... .Find(x, LookIn:=xlValues) Is this the only way? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
what is the best way to find a value in a sheet in VBA?
Easiest is to just try it, but there aren't significantly faster ways. It
should only look at Used cells, not the entire column, I would think - at least it is fast enough to indicate that - no way to prove it. -- Regards, Tom Ogilvy "serdar" wrote in message ... if i look up to some columns with: Columns("A:D").Find(x, LookIn:=xlValues) ,does this looks "all" rows in columns a to d, or just the formatted ones? If first is true, does this takes too much time than looking up by giving the actual workspace as a range(not a good solution for me)? thanks. "Tom Ogilvy" , haber iletisinde şunları ... It is the most general way if you don't want to loop. You can also use the built in lookup, vlookup, match type functions. -- Regards, Tom Ogilvy "serdar" wrote in message ... .Find(x, LookIn:=xlValues) Is this the only way? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
what is the best way to find a value in a sheet in VBA?
The other way is not open ended so im keeping the "entire column" approach.
I may misremember but i was using this with multiple operations once and it taked too long to search. My prediction is Excel would look only the used cells though. thanks for ur help for now. "Tom Ogilvy" , haber iletisinde şunları ... Easiest is to just try it, but there aren't significantly faster ways. It should only look at Used cells, not the entire column, I would think - at least it is fast enough to indicate that - no way to prove it. -- Regards, Tom Ogilvy "serdar" wrote in message ... if i look up to some columns with: Columns("A:D").Find(x, LookIn:=xlValues) ,does this looks "all" rows in columns a to d, or just the formatted ones? If first is true, does this takes too much time than looking up by giving the actual workspace as a range(not a good solution for me)? thanks. "Tom Ogilvy" , haber iletisinde şunları ... It is the most general way if you don't want to loop. You can also use the built in lookup, vlookup, match type functions. -- Regards, Tom Ogilvy "serdar" wrote in message ... .Find(x, LookIn:=xlValues) Is this the only way? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
what is the best way to find a value in a sheet in VBA?
Please help with the coding for .Find
Example, within a range A1:A10 I would have various values, apples, cans, abc, toys, etc. When I write Range("A1:A10").find("abc", LookIn:=xlValues) I get a compile error of expected = What am I missing? "Tom Ogilvy" wrote: It is the most general way if you don't want to loop. You can also use the built in lookup, vlookup, match type functions. -- Regards, Tom Ogilvy "serdar" wrote in message ... .Find(x, LookIn:=xlValues) Is this the only way? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
what is the best way to find a value in a sheet in VBA?
Try something like:
Sub findit() Dim fndRng As Range With Range("A1:A10") Set fndRng = .Find("abc", LookIn:=xlValues) End With If Not fndRng Is Nothing Then MsgBox "Found in cell: " & fndRng.Address Else MsgBox "Not Found" End If End Sub Hope this helps Rowan AG wrote: Please help with the coding for .Find Example, within a range A1:A10 I would have various values, apples, cans, abc, toys, etc. When I write Range("A1:A10").find("abc", LookIn:=xlValues) I get a compile error of expected = What am I missing? "Tom Ogilvy" wrote: It is the most general way if you don't want to loop. You can also use the built in lookup, vlookup, match type functions. -- Regards, Tom Ogilvy "serdar" wrote in message ... .Find(x, LookIn:=xlValues) Is this the only way? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
what is the best way to find a value in a sheet in VBA?
Got it!
Sub test() With Range("A1:A10").find("xyz", LookIn:=xlValues).Select End With End Sub "AG" wrote: Please help with the coding for .Find Example, within a range A1:A10 I would have various values, apples, cans, abc, toys, etc. When I write Range("A1:A10").find("abc", LookIn:=xlValues) I get a compile error of expected = What am I missing? "Tom Ogilvy" wrote: It is the most general way if you don't want to loop. You can also use the built in lookup, vlookup, match type functions. -- Regards, Tom Ogilvy "serdar" wrote in message ... .Find(x, LookIn:=xlValues) Is this the only way? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
what is the best way to find a value in a sheet in VBA?
Just be aware that this will result in an error if xyz is not found.
Regards Rowan AG wrote: Got it! Sub test() With Range("A1:A10").find("xyz", LookIn:=xlValues).Select End With End Sub "AG" wrote: Please help with the coding for .Find Example, within a range A1:A10 I would have various values, apples, cans, abc, toys, etc. When I write Range("A1:A10").find("abc", LookIn:=xlValues) I get a compile error of expected = What am I missing? "Tom Ogilvy" wrote: It is the most general way if you don't want to loop. You can also use the built in lookup, vlookup, match type functions. -- Regards, Tom Ogilvy "serdar" wrote in message ... .Find(x, LookIn:=xlValues) Is this the only way? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
what is the best way to find a value in a sheet in VBA?
You will get an error if the value is not found. Best to do it as Rowan
illustrated. -- Regards, Tom Ogilvy "AG" wrote in message ... Got it! Sub test() With Range("A1:A10").find("xyz", LookIn:=xlValues).Select End With End Sub "AG" wrote: Please help with the coding for .Find Example, within a range A1:A10 I would have various values, apples, cans, abc, toys, etc. When I write Range("A1:A10").find("abc", LookIn:=xlValues) I get a compile error of expected = What am I missing? "Tom Ogilvy" wrote: It is the most general way if you don't want to loop. You can also use the built in lookup, vlookup, match type functions. -- Regards, Tom Ogilvy "serdar" wrote in message ... .Find(x, LookIn:=xlValues) Is this the only way? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
what is the best way to find a value in a sheet in VBA?
Thanks to both Rowan & Tom for the additional insight.
"Rowan Drummond" wrote: Try something like: Sub findit() Dim fndRng As Range With Range("A1:A10") Set fndRng = .Find("abc", LookIn:=xlValues) End With If Not fndRng Is Nothing Then MsgBox "Found in cell: " & fndRng.Address Else MsgBox "Not Found" End If End Sub Hope this helps Rowan AG wrote: Please help with the coding for .Find Example, within a range A1:A10 I would have various values, apples, cans, abc, toys, etc. When I write Range("A1:A10").find("abc", LookIn:=xlValues) I get a compile error of expected = What am I missing? "Tom Ogilvy" wrote: It is the most general way if you don't want to loop. You can also use the built in lookup, vlookup, match type functions. -- Regards, Tom Ogilvy "serdar" wrote in message ... .Find(x, LookIn:=xlValues) Is this the only way? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can find sheet on workbook have so many sheet ? | Excel Worksheet Functions | |||
find last row in a sheet | Excel Discussion (Misc queries) | |||
Find data from one sheet in another sheet | Excel Worksheet Functions | |||
how to find and copy values on sheet 2, based on a list on sheet 1 | Excel Programming | |||
Cant find sheet | Excel Programming |