Excel Formula into VBA code error

Question

I am getting a few errors with VBA code that I have written to convert from a formula based query.

Cell.Offset(0, 37).Value = Application.WorksheetFunction.IfError(Application.WorksheetFunction.Index((Sheets("CP INFO").Range("N6:AK570")), Application.WorksheetFunction.Match(Cell.Offset(0, 36).Value, Sheets("CP INFO").Range("N6:AK570"), 0), 1), "No SFH")

The error is stating unable to get the 'Match property of the worksheetfunction class. The original formula for the above code is - =IFERROR(INDEX('CP INFO'!$N$6:AK$570,MATCH(VALUE(AK4),'CP INFO'!$N$6:$N$570,0),1),"NO SFH") SO I cannot see what is wrong with it.

The next error I am getting is with the below code.

Cell.Offset(0, 39).Value = Application.WorksheetFunction.IfError(Left(Application.WorksheetFunction.IfError(Left(Right(Cell.Offset(0, 24), _
Len(Cell.Offset(0, 24)) - Application.WorksheetFunction.Find("(", Cell.Offset(0, 24))), (Len(Cell.Offset(0, 24)) - Application.WorksheetFunction.Find("-", Cell.Offset(0, 24))) + 1), ""), _
Application.WorksheetFunction.Find("-", Application.WorksheetFunction.IfError(Left(Right(Cell.Offset(0, 24), Len(Cell.Offset(0, 24)) - _
 Application.WorksheetFunction.Find("(", Cell.Offset(0, 24))), (Len(Cell.Offset(0, 24)) - Application.WorksheetFunction.Find("-", Cell.Offset(0, 24))) + 1), "")) - 1), _
Application.WorksheetFunction.IfError(Left(Right(Cell.Offset(0, 24), Len(Cell.Offset(0, 24)) - _
Application.WorksheetFunction.Find("(", Cell.Offset(0, 24))), (Len(Cell.Offset(0, 24)) - Application.WorksheetFunction.Find("-", Cell.Offset(0, 24))) + 1), ""))

This error is relating to the find function stating unable to find the property. I'm a real novice when it comes to these functions within VBA so no doubt I am doing something wrong or missing something?

Original formula this code has come from.

=IFERROR(LEFT(IFERROR(LEFT(RIGHT(DATA2!Y4,LEN(DATA2!Y4)-FIND("(",DATA2!Y4)),(LEN(DATA2!Y4)- 
FIND("-",DATA2!Y4))+1),""),FIND("-",IFERROR(LEFT(RIGHT(DATA2!Y4,LEN(DATA2!Y4)- 
FIND("(",DATA2!Y4)),(LEN(DATA2!Y4)-FIND("-",DATA2!Y4))+1),""))-1),IFERROR 
(LEFT(RIGHT(DATA2!Y4,LEN(DATA2!Y4)-FIND("(",DATA2!Y4)),(LEN(DATA2!Y4)-FIND("-",DATA2!Y4))+1),""))

The design of this macro is to loop through roughly 3-5k rows of data and produce a report of data pre sorted. The Cell is the current row and Column A The offset is to post values in corresponding Columns if the criteria is met.

Hopefully someone can assist.

Regards Alan

It is better practice to not use WorksheetFunction in vba code, but to write the code that does what the function does. If you insist on using the function, then look into worksheet().Evaluate — Scott Craner
– Scott Craner, Commented Nov 6, 2017 at 14:49
Thanks @ScottCraner I have not used Evaluate before so I will read up on it. Just a quick search I have seen comments to state this is a slow method to use? — Alan Treanor
– Alan Treanor, Commented Nov 6, 2017 at 14:57
No slower than using worksheetformula. It would be quicker to turn it into true vba and use arrays instead of referring to the worksheet. I would take this opportunity to learn to write correct code, using arrays. — Scott Craner
– Scott Craner, Commented Nov 6, 2017 at 14:59
@ScottCraner - I didn't want to ask as a new question but I have setup a function to cycle through text and if the "-" char is found then it will run the loop again until it returns a final value (this is for the second if error formula). This is working perfectly apart from the final answer is keeping 1 "-" char. The final formula would be - DrID has been set and then loops through and DrID = Left(DrID, Val -1) as the final run would give the correct value. But it is giving an error whenever I try add -1. — Alan Treanor
– Alan Treanor, Commented Nov 6, 2017 at 18:27
Ignore the comment I answered it myself after typing that by thinking of it in a different way. — Alan Treanor
– Alan Treanor, Commented Nov 6, 2017 at 18:29

Gary's Student · Accepted Answer · 2017-11-06 15:51:29Z

0

You can't use IfError() like that in VBA, instead something like:

v = "No SFH"
On Error Resume Next
    With Application.WorksheetFunction
        v = .Index((Sheets("CP INFO").Range("N6:AK570")), .Match(Cell.Offset(0, 36).Value, Sheets("CP INFO").Range("N6:AK570"), 0), 1)
    End With
On Error GoTo 0
Cell.Offset(0, 37).Value = v

NOTE:

As Scott points out, the arguments of MATCH() also require correction.

edited Nov 6, 2017 at 15:51

answered Nov 6, 2017 at 15:09

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Scott Craner Over a year ago

I don't think Match can use a 2D range.

Gary's Student Over a year ago

@ScottCraner what you say is true, but then you would get No SFH

Scott Craner Over a year ago

Yes you would get it always, no matter what Cell.Offset(0, 36).Value is.

Collectives™ on Stack Overflow

Excel Formula into VBA code error

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related