Visual Basic Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lj - 19 Dec 2006 23:28 GMT

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

Reply to this Message

Nick Hodge - 19 Dec 2006 23:36 GMT

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

Signature

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
www.nickhodge.co.uk

> 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
[quoted text clipped - 4 lines]
> go to a particular cell where a word is located within a pivot table or
> spreadsheet (a find function)? Thanks for your help

Reply to this Message

lj - 19 Dec 2006 23:47 GMT

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?

> lj
>
[quoted text clipped - 28 lines]
> > go to a particular cell where a word is located within a pivot table or
> > spreadsheet (a find function)? Thanks for your help

Reply to this Message

Dave Peterson - 20 Dec 2006 00:00 GMT

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

> 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
[quoted text clipped - 32 lines]
> > > go to a particular cell where a word is located within a pivot table or
> > > spreadsheet (a find function)? Thanks for your help

Signature

Dave Peterson

Reply to this Message

lj - 20 Dec 2006 00:22 GMT

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?

> Yep.
>
[quoted text clipped - 45 lines]
> > > > go to a particular cell where a word is located within a pivot table or
> > > > spreadsheet (a find function)? Thanks for your help

Reply to this Message

Dave Peterson - 20 Dec 2006 00:29 GMT

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.

> I think this is what i'm looking for - is there a way to switch the
>
[quoted text clipped - 57 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

lj - 20 Dec 2006 00:53 GMT

Thanks!! That works great!

> Nope.
>
[quoted text clipped - 67 lines]
> > >
> > > Dave Peterson

Reply to this Message

Nick Hodge - 20 Dec 2006 07:58 GMT

Dave

Thanks for stepping in overnight ;-)

Signature

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
www.nickhodge.co.uk