Incorrect usage of VBA WSfunction
 

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

Incorrect usage of VBA WSfunction
 

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.

--
Les Torchia-Wells


"Jim May" wrote:


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

Incorrect usage of VBA WSfunction
 

Look at InStr in VBA's help.



Jim May wrote:

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

--

Dave Peterson

Incorrect usage of VBA WSfunction
 

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

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

--
Regards,
Tom Ogilvy


"Jim May" wrote:


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

Incorrect usage of VBA WSfunction
 

Got it !!
Tks,
Jim

"Tom Ogilvy" wrote:

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

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

--
Regards,
Tom Ogilvy


"Jim May" wrote:


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