Incorrect usage of VBA WSfunction

J

Jim May

..... Omission of lines
str1 = Right(Target.Formula, Len(Target.Formula) - 1)
If WorksheetFunction.Find("!", str1) = Err Then Exit Sub << Not Right !!

line 2 above tests for - If the formula str1 DOES NOT refer to another Sheet
(with !)
then exit Sub

But what is (Right)?

TIA,

Jim
 
L

Les

Since you are running in VBA, use the INSTR function to search for "!"

i.e.
If Target.Formula <> "" Then
str1 = Right(Target.Formula, Len(Target.Formula) - 1)
If InStr(str1, "!") = 0 Then Exit Sub
End If

Please not that "!" does not ensure the formula refers to a separate
worksheet, it could very well refer to the active worksheet.
 
D

Dave Peterson

Look at InStr in VBA's help.



Jim said:
.... Omission of lines
str1 = Right(Target.Formula, Len(Target.Formula) - 1)
If WorksheetFunction.Find("!", str1) = Err Then Exit Sub << Not Right !!

line 2 above tests for - If the formula str1 DOES NOT refer to another Sheet
(with !)
then exit Sub

But what is (Right)?

TIA,

Jim
Click to expand...
 
T

Tom Ogilvy

iserror test for an error, but use the vba Instr for this

If Instr(1,str1,"!",vbTextcompare) = 0 Then Exit Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Coding Help for Beginner 0
Removal of " "'s in my VBA routine 4
Replacing the formula text's substring 4
Import 1, 3, 6, and 12 month returns for a list of mutual funds 8
Code Fails at this Line: .Refresh BackgroundQuery:=False 1
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Sequential Number Generator Macro 3
2 Macros in VBA??? 3

Top