Udf

[hodges5378]

I'm trying to clean up some formulas by creating a user defined function that replaces #n/a with 0. The formulas I have currently are doing this work, but I wanted to know if there was a way to create a UDF that will do this for me. For example here's my formula:

=IF(ISNA(VLOOKUP($A6,Sales_Actuals,7,FALSE)),0,VLOOKUP($A6,Sales_Actuals,7,FALSE))

Is there a way to create a UDF to where I am left with only the VLOOKUP formula and not the ISNA? I want that work being done in the background by the UDF.

Thanks!

[royUK]

What do you mean? Do you want to replace the ISNA witha UDF?

[hodges5378]

I'm not familiar with UDF's so to be honest I'm not exactly sure how to explain it in a way that is understandable. My ultimate goal is to not have such a long formula because we're going to be doing the same formula throughout the worksheet, so I didn't know if there was a way to create a function that will do that in the background and we just have to enter the function in the formula bar. Sorry if this is confusing.

[hodges5378]

Here is where I'm at.

Function John(Cell1 As Range, Range1 As Range, Column1 As Integer, Bool1 As Boolean) As String
John = =IF(ISNA(VLOOKUP(Cell1,Range1,Column1,Bool1)),0,VLOOKUP(Cell1,Range1,Column1,Bool1))
End Function

I'm getting an error that says compiled error: expected: expression.

Any thoughts on how to fix this function?

Thanks.

[Leith Ross]

Hello hodges5378,

Here is the UDF macro to replace the IF statement worksheet function you wrote. This takes the same parameters as VLookup. If there are any errors, zero is returned.

Please Login or Register  to view this content.

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

Sincerely,
Leith Ross