Trending September 2023 # How To Use Iferror Function In Vba? # Suggested October 2023 # Top 10 Popular | Nhahang12h.com

Trending September 2023 # How To Use Iferror Function In Vba? # Suggested October 2023 # Top 10 Popular

You are reading the article How To Use Iferror Function In Vba? updated in September 2023 on the website Nhahang12h.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 How To Use Iferror Function In Vba?

VBA IFERROR

A written code many times gives the error and chances of getting an error in complex error are quite high. Like excel has IFERROR function which is used where there are chances of getting an error. IFERROR changes the error message into other text statements as required and defined by the user. Similarly, VBA IFERROR functions same as the IFERROR function of Excel. It changes the error message into the defined text by the user.

This error mostly occurs when we perform any mathematical function or if we are mapping any data which is in a different format. IFERROR handles many errors, some of them are:

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

#VALUE!, #N/A, #DIV/0!, #REF!, #NUM!, #NULL! And #NAME?

Example #1

Now this IFERROR function can also be implemented in VBA. Now to compare the results from Excel with VBA IFERROR we will insert a column where we will apply VBA IFERROR statement as shown below.

Now in a newly opened module, write Sub-category with any name. Here we have given the name of the operating function as shown below.

Code:

Sub

VBA_IfError()

End Sub

Now with the help of ActiveCell, we will select the first reference cell and then directly use IFERROR Formula with reference cell (RC) -2 divided by -1 cell numbers. Which means we are dividing cell A by cell B in the first argument. And in the second argument write any statement which we want in place of error. Here we will use the same state which we have seen above i.e. “No Product Class”.

Code:

Sub

VBA_IfError() ActiveCell.FormulaR1C1 = "=IFERROR(RC[-2]/RC[-1],""No Product Class"")"

End Sub

Now select the range cell which would be our denominator. Here we have selected cell C2.

Code:

Sub

VBA_IfError() ActiveCell.FormulaR1C1 = "=IFERROR(RC[-2]/RC[-1],""No Product Class"")" Range("C2").Select

End Sub

Now to drag the formula to below cells where we need to apply IFERROR till the table has the values.

Code:

Sub

VBA_IfError() ActiveCell.FormulaR1C1 = "=IFERROR(RC[-2]/RC[-1],""No Product Class"")" Range("C2").Select Selection.End(xlDown).Select

End Sub

Now to come to the last cell of the column with the help of command Range where we need to drag the IFERROR formula. Here our limit ends at cell D6.

Code:

Sub

VBA_IfError() ActiveCell.FormulaR1C1 = "=IFERROR(RC[-2]/RC[-1],""No Product Class"")" Range("C2").Select Selection.End(xlDown).Select Range("D6").Select

End Sub

Now to drag the applied IFERROR down to all the applicable cells select the Range from End (or Last) cell to Up to the applied formula cell by End(xlUp) command under Range.

Code:

Sub

VBA_IfError() ActiveCell.FormulaR1C1 = "=IFERROR(RC[-2]/RC[-1],""No Product Class"")" Range("C2").Select Selection.End(xlDown).Select Range("D6").Select Range(Selection, Selection.End(xlUp)).Select

End Sub

As we do Ctrl + D to drag the up cell values to all selected cells in excel, here in VBA, Selection.FillDown is used to fill the same up cells values in all selected cells.

Code:

Sub

VBA_IfError() ActiveCell.FormulaR1C1 = "=IFERROR(RC[-2]/RC[-1],""No Product Class"")" Range("C2").Select Selection.End(xlDown).Select Range("D6").Select Range(Selection, Selection.End(xlUp)).Select Selection.FillDown

End Sub

As we can see above, in column D starting from Cell 2 to 6 we got our results.

There is another format and way to apply IFERROR in VBA. For this, we will consider another set of data as shown below. Below we have sales data of some products in column A and quality sold in column B. And we need map this data at cell F2 with reference to product type Laptop. Now data in the first table has #N/A in cell B3 with for Laptop quantity sold out which means that source data itself has an error. And if we look up the data from the first table to Cell F2 we will get same #N/A here.

For this open a new module in VBA and write Subcategory with the name of a performed function.

Code:

Sub

VBA_IfError2()

End Sub

Select range where we need to see the output or directly activate that cell by ActiveCell followed by dot (.) command line as shown below.

Now select FormulaR1C1 from the list which is used for inserting any excel function.

Now use the same formula as used in excel function in VBA as well.

Code:

Sub

VBA_IfError2() ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],R[-1]C[-5]:R[3]C[-4],2,0),""Error In Data"")"

End Sub

Now select the cell where we need to see the output in Range. Here we have selected cell F3.

Code:

Sub

VBA_IfError2() ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],R[-1]C[-5]:R[3]C[-4],2,0),""Error In Data"")" Range("B8").Select

End Sub

Now compile and run complete code using F5 key or manually and see the result as shown below.

As we can see in the above screenshot, the output of product type Laptop, from first table #N/A is with error text “Error In Data” as defined in VBA code.

Pros of VBA IFERROR Function

It takes little time to apply the IFERROR function through VBA.

Result from Excel insert function and VBA IFERROR coding, both are the same.

We can format or paste special the applied formula as well to avoid any further problem.

Cons of VBA IFERROR Function

IFERROR with this method, referencing the range of selected cell may get disturbed as the table has limited cells.

Things To Remember

We can record the macro and modify the recorded coding as per our need.

Always remember to save the file as Macro-Enabled Excel, so that we can use the applied or created macro multiple times without any issue.

Make sure you remember to compile the complete code before ending the command function. By this, we can avoid or correct any error incurred.

Best way to avoid any changes after running the code is to paste special the complete cells so that there will not be any formula into the cell.

Recommended Articles

This has been a guide to Excel VBA IFERROR Function. Here we discussed how to use IFERROR Function in VBA along with some practical examples and downloadable excel template. You can also go through our other suggested articles–

You're reading How To Use Iferror Function In Vba?

Update the detailed information about How To Use Iferror Function In Vba? on the Nhahang12h.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!