This is easily handled with a simple function that checks for a potential error instead of triggering an error. Code first, and then an explanation:
Public Sub MultiplyValues()
'
' MultiplyValues Macro
'
' =IFERROR(IF(A2="NULL",B2*C2,A2*B2*C2),0)
' Forumala is in D
Dim Target As Range
' The line below implicitly uses the ActiveSheet. If this is intended, qualify your range with `ActiveSheet.Range`, otherwise
' you will want to identify the proper sheet.
Set Target = Range("A2", Range("A2").End(xlDown))
Dim Cell As Range
For Each Cell In Target.Cells
' No need to use an error handler. Avoiding errors is always preferred over relying on the error handler. In this case, let's use a function.
Cell.Offset(0, 3).Value = GetFormulaValue(Cell)
Next Cell
End Sub
Private Function GetFormulaValue(ByVal InputCell As Range) As Double
If Not IsNumeric(InputCell.Value) Then
' Be sure to explicitly access the value property of the range object.
GetFormulaValue = InputCell.Offset(0, 1).Value * InputCell.Offset(0, 2).Value
' I am adding this in case you want to run a separate calculation if the cell-value is 0).
' ElseIf InputCell.Value = 0 Then
' Your code here
Else
' While order of operations would automatically calculate X * Y and then XY * Z, I prefer using parentheses
' to make my code even clearer.
GetFormulaValue = (InputCell.Offset(0, 0) * InputCell.Offset(0, 1)) * InputCell.Offset(0, 2)
End If
End Function
Public vs Private
First I would like to note the use of a Public subroutine along with a Private function. This basically just means that the MultiplyValues subroutine is accessible by other routines/the macro's list. On the other hand, the function is only seen by other code in the same module (so we don't risk the wrong code using it, and potentially returning a bad value). This is a little bit more advanced compared to where you are, but it is a good practice to get into early.
Variable Declarations
I noticed that you were using Hungarian Notation (HN). I would venture to assume we all go through this phase in the SO VBA community, but the sooner you break that habit, the better. It is far better to properly name variables than to use HN to denote the types. For this purpose, the names were easy. For larger projects it can be a bit more complicated. The sooner you avoid the habit though, the better.
The Good Stuff
Avoiding Errors Before They Happen
Error handling in VBA is not very robust. It works, but oftentimes it is misused. I am of the mindset that error handling (in the line label sense) should be avoided at all costs. The best way to accomplish this is to use logic to your advantage, and to wrap repeated operations in a single function.
In this case, the appropriate method was easy. The function takes an input cell, and returns a value based on the contents of the cell. While you initially checked for Null the function checks for IsNumeric since this will catch Null as well as anything else that isnt a number (since XYZ isn't Null but will throw an error if multiplied by a number).
Why Use Functions?
I like to think of functions as magical creatures that really are under-appreciated by newer programmers. Maybe I am hyping them up a bit. Maybe not. What I can say for sure is I used to avoid them. Then, someone came along (cough ThunderFrame cough) and lightly-reprimanded my avoidance of using single-functions for repeated steps (I had the same Format(X, Y) command in numerous places. He reminded me that, should that command ever change, I would then have to find every single place it lived and fix it. Simple advice, simple reminder, but it changed my approach to using Functions.
I tend to ramble, so the point here is this: When you have some operation that needs to be done in multiple places (or on multiple conditions) you should strongly consider a function. That way, should the operation change, you can fix it in the function, and not in all of the places that you are calculating the operation manually. Functions also allow you to return different types of values. Declare a Function as a Variant and you can return a vbNullString a 0 a "ThisIsABadValuePleaseIgnoreIt". Whatever you want to do. They allow greater control over the flow of your code.
Final Tidbits
As I do in many of my CR posts, I recommend using RubberDuck, especially when you're starting out. You can find it here: http://rubberduckvba.com/. It helps for finding those little things you do without knowing it (Implicit reference to ActiveWorksheet and .Value for example) and help you learn better habits. Over time, these small changes in habits can make or break a programmer.
Best of luck on this.
