Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11
Default what is the best way to find a value in a sheet in VBA?

..Find(x, LookIn:=xlValues)

Is this the only way?


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11
Default 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   Report Post  
Posted to microsoft.public.excel.programming
AG AG is offline
external usenet poster
 
Posts: 54
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 414
Default 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   Report Post  
Posted to microsoft.public.excel.programming
AG AG is offline
external usenet poster
 
Posts: 54
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 414
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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   Report Post  
Posted to microsoft.public.excel.programming
AG AG is offline
external usenet poster
 
Posts: 54
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how can find sheet on workbook have so many sheet ? Huy Nguyen Excel Worksheet Functions 1 November 22nd 07 09:45 AM
find last row in a sheet Janis Excel Discussion (Misc queries) 3 November 7th 07 04:21 AM
Find data from one sheet in another sheet [email protected] Excel Worksheet Functions 3 August 21st 06 07:08 PM
how to find and copy values on sheet 2, based on a list on sheet 1 evanmacnz Excel Programming 4 February 7th 05 09:33 PM
Cant find sheet Jim73 Excel Programming 2 January 12th 05 02:59 PM


All times are GMT +1. The time now is 11:06 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"