VB Code

[Hal]

I spent what seemed a lifetime learning excel macro language and now think
its about time I embraced this new fangled vb code. I cannot seem to grasp
how error checking works in vb, it was so simple in macro language. I'm sure
its as simple in vb, but I need some guidance.


Can somebody please help with the vb code for the following macro4 code.

A1=SELECT("Range_1")
A2=ERROR(FALSE)
A3=FORMULA.FIND("Something")
A4=IF(A3=TRUE,GOTO(A7))
A5=ALERT("Not found")
A6=HALT()
A7=ALERT("Found")
A8=HALT()

 

[Bob Phillips]

Dim c As Range

On Error Resume Next
Set c = Range("Range_1").Find("Something")
On Error Goto 0
If c Is Nothing Then
Msgbox "Not Found
Else
Msgbox "Found"
End If

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

This code is a little more than you asked for but it serves to illustrate
what you want. It creates a worksheet object that is the same as the active
sheet. You could make it equal to any sheet in the book. It creates a range
object (rngToSearch) that is cells A1 to A100 on the worksheet. It then tries
to set the last range object (rngFound) equal to the a cell with the word
"Something" in it. It then determines if rngFound was successfully set (if it
wasn't then it will still be nothing). Finally it displays message boxes with
the results.

Sub Test()
Dim wksCurrent As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range

Set wksCurrent = ActiveSheet
Set rngToSearch = wksCurrent.Range("A1:A100")
Set rngFound = rngToSearch.Find("Something")

If rngFound Is Nothing Then
MsgBox "Not Found"
Else
MsgBox "Found Something at address" & rngFound.Address
End If
End Sub

HTH

 

[Guest]

You know how I love to bug you Bob. What is the point to your statement

on error resume next

If the range object can not be set then c remains as nothing and no error is
generated. I tried your code both ways and it works just fine without the
error statements.

 

[GysdeJongh]

Dim c As Range

On Error Resume Next
Set c = Range("Range_1").Find("Something")
On Error Goto 0
If c Is Nothing Then
Msgbox "Not Found
Else
Msgbox "Found"
End If

--

HTH

RP
(remove nothere from the email address if mailing direct)

Does someone know if VBA is also abandoned by Microsoft for a .NET version ??

Gys

 

[Dave Peterson]

I'm guessing muscle memory <bg>. Sometimes fingers just take over for the
brain.

(Well, that explains why I sometimes do this!)

 

[Guest]

I am still waiting for the day that my brain takes over for my mouth. That
will keep me out of lots of trouble...

 

[Bob Phillips]

Have a look at this discussion:

http://www.dicks-blog.com/archives/2005/03/09/support-classic-vb/#comments

Watch for wrap around dropping the final s

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

You know how I love to bug you Bob. What is the point to your statement

on error resume next

If the range object can not be set then c remains as nothing and no error is
generated. I tried your code both ways and it works just fine without the
error statements.

Not for me it doesn't, it fails nastily with 'Method Range 'of Object'
_Global Failed'.

It is not the Set c that is failing, but the Find.



Bob

 

[Hal NineThousand]

Many Thanks for all your replies, The trouble with being an old 1960's
supercomputer, I just can't keep up with all the new technology since
2001

 

[GysdeJongh]

Thank you for this link ! . Peoples comments like : "For an VBA speaker,
learning VB.NET is like learning Klingon" make me worry as we also use a lot of
VBA just for simple tasks

Gys