Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet functions in VBA | Excel Programming | |||
UDF x worksheet functions | Excel Worksheet Functions | |||
Worksheet functions | Excel Worksheet Functions | |||
Public Functions As Worksheet Available Functions | Excel Programming | |||
worksheet functions | Excel Programming |