Differences using worksheet functions in worksheet and via VBA
 

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.

Differences using worksheet functions in worksheet and via VBA
 

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.

Differences using worksheet functions in worksheet and via VBA
 

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.

Differences using worksheet functions in worksheet and via VBA
 

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.