Visual Basic Question

L

lj

I'm trying to put together a macro that looks to see if a particular
word exists within a range in an excel spreadsheet and continues with
specific instructions if it does. I know an if / then statment can be
used to look within one particular cell, can it be used to look within
a range of cells or better yet within a pivot table? If so what is
what is the code for this? Also, is there a way to have visual basic
go to a particular cell where a word is located within a pivot table or
spreadsheet (a find function)? Thanks for your help
 
N

Nick Hodge

lj

Does this help

Sub FindSomething()
Dim fnd As Range
Dim wks As Worksheet
Set wks = ActiveSheet
Set fnd = wks.Range("A1:A1000").Find("Nick")
If Not fnd Is Nothing Then
MsgBox "This will happen if Nick is found"
Exit Sub
End If
MsgBox "This will happen if Nick is not found"
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
www.nickhodge.co.uk
 
L

lj

Thanks, this kind of helps. Is there a way to continue on in the macro
if the string is found or go directly to the cell that the string is
in?
 
D

Dave Peterson

Yep.

Sub FindSomething()
Dim fnd As Range
Dim wks As Worksheet
Set wks = ActiveSheet
Set fnd = wks.Range("A1:A1000").Find("Nick")
If fnd Is Nothing Then
MsgBox "Not found"
else
fnd.select
End Sub
 
L

lj

I think this is what i'm looking for - is there a way to switch the

If fnd Is Nothing Then

to if fnd is something then else

is there a statement for if fnd is something?
 
D

Dave Peterson

Nope.

You can use what Nick suggested:
If Not fnd Is Nothing Then

But I find that too negative <vbg>.

I just like to add a comment "'do nothing" as a reminder that I'm not doing
anything in that branch of the if/then/else statement.
 
L

lj

Thanks!! That works great!

Dave said:
Nope.

You can use what Nick suggested:
If Not fnd Is Nothing Then

But I find that too negative <vbg>.

I just like to add a comment "'do nothing" as a reminder that I'm not doing
anything in that branch of the if/then/else statement.
Click to expand...
 
Top