Visual Basic Question

[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

 

[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
(e-mail address removed)
www.nickhodge.co.uk

 

[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?

 

[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

 

[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?

 

[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.

 

[lj]

Thanks!! That works great!

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.

 

[Nick Hodge]

Dave

Thanks for stepping in overnight ;-)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[Dave Peterson]

That's one of the best things about the newsgroups. There's always someone
awake. (Well, as awake as I get--for me.)

Dave

Thanks for stepping in overnight ;-)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk