When I use this function in a worksheet (referencing a cell that contains no
spaces), it returns "Is Error" as it should

=IF(ISERROR(LEFT(TRIM(A3),FIND(" ",TRIM(A3)-1))),"Is Error","Is Not Error")

However when I use the VBA version, it throws an Error 1004, "Unable to get
the Find property of the WorksheetFunction class".

If Not IsError(Application.WorksheetFunction.Find(" ",
Trim(ActiveCell.Offset(0, -1)))) Then

I'm trying to parse a very irregular text file into columns.

try:
If Not IsError(Application.Find(" ", Trim(ActiveCell.Offset(0, -1)))) Then

or check vba help for the Instr function and use that

or try
On Error Resume Next
x=IsError(Application.WorksheetFunction.Find(" ", Trim(ActiveCell.Offset(0,
-1))))
If Err.Number <> 0 Then
Err.Clear
'There is an error, do Something
Else
'No Error
End If
On Error Goto 0

"dbGeezer" wrote:

> When I use this function in a worksheet (referencing a cell that contains no
> spaces), it returns "Is Error" as it should
>
> =IF(ISERROR(LEFT(TRIM(A3),FIND(" ",TRIM(A3)-1))),"Is Error","Is Not Error")
>
> However when I use the VBA version, it throws an Error 1004, "Unable to get
> the Find property of the WorksheetFunction class".
>
> If Not IsError(Application.WorksheetFunction.Find(" ",
> Trim(ActiveCell.Offset(0, -1)))) Then
>
> I'm trying to parse a very irregular text file into columns.

Thanks JMB. The first one worked. I guess that the worksheetfunction class
operates slightly differently than expected.

"JMB" wrote:

> try:
> If Not IsError(Application.Find(" ", Trim(ActiveCell.Offset(0, -1)))) Then
>
> or check vba help for the Instr function and use that
>
> or try
> On Error Resume Next
> x=IsError(Application.WorksheetFunction.Find(" ", Trim(ActiveCell.Offset(0,
> -1))))
> If Err.Number <> 0 Then
> Err.Clear
> 'There is an error, do Something
> Else
> 'No Error
> End If
> On Error Goto 0
>
> "dbGeezer" wrote:
>
> > When I use this function in a worksheet (referencing a cell that contains no
> > spaces), it returns "Is Error" as it should
> >
> > =IF(ISERROR(LEFT(TRIM(A3),FIND(" ",TRIM(A3)-1))),"Is Error","Is Not Error")
> >
> > However when I use the VBA version, it throws an Error 1004, "Unable to get
> > the Find property of the WorksheetFunction class".
> >
> > If Not IsError(Application.WorksheetFunction.Find(" ",
> > Trim(ActiveCell.Offset(0, -1)))) Then
> >
> > I'm trying to parse a very irregular text file into columns.

it also does that w/vlookup, hlookup, probably match, and (from a post by
Alan Beban), it appears to mess up Index(SomeRange, ,2 ) where the second
parameter is missing (but s/b assumed to be 0)

"dbGeezer" wrote:

> Thanks JMB. The first one worked. I guess that the worksheetfunction class
> operates slightly differently than expected.
>
> "JMB" wrote:
>
> > try:
> > If Not IsError(Application.Find(" ", Trim(ActiveCell.Offset(0, -1)))) Then
> >
> > or check vba help for the Instr function and use that
> >
> > or try
> > On Error Resume Next
> > x=IsError(Application.WorksheetFunction.Find(" ", Trim(ActiveCell.Offset(0,
> > -1))))
> > If Err.Number <> 0 Then
> > Err.Clear
> > 'There is an error, do Something
> > Else
> > 'No Error
> > End If
> > On Error Goto 0
> >
> > "dbGeezer" wrote:
> >
> > > When I use this function in a worksheet (referencing a cell that contains no
> > > spaces), it returns "Is Error" as it should
> > >
> > > =IF(ISERROR(LEFT(TRIM(A3),FIND(" ",TRIM(A3)-1))),"Is Error","Is Not Error")
> > >
> > > However when I use the VBA version, it throws an Error 1004, "Unable to get
> > > the Find property of the WorksheetFunction class".
> > >
> > > If Not IsError(Application.WorksheetFunction.Find(" ",
> > > Trim(ActiveCell.Offset(0, -1)))) Then
> > >
> > > I'm trying to parse a very irregular text file into columns.