Incorrect usage of VBA WSfunction

[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

[Dave Peterson]

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

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

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

[Tom Ogilvy]

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

[Jim May]

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