what is the best way to find a value in a sheet in VBA?
 

..Find(x, LookIn:=xlValues)

Is this the only way?

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?

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?

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?

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?

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?

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?

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?

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?

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?

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?