Dividing values in one column with another column and setting value based on another cell-CodePudding

I am fairly new to VBA and need some help in understanding what i am doing wrong.

I have 3 columns. If the value in the first column is zero, i want to set the third column as "N/A" . If the value in first column is not zero, then the third column should be second column / first column.

First column is c4:c6

Second column is d4:d6

Third column is e4:e6

Dim k As Range

For Each k In ws.Range("c4:c6")
    If k = 0 Then
        Range("e4:e6").Value = "N/A"
    Else
        Range("e4:e6").Formula = "=d4/c4"
    End If
Next

CodePudding user response：

Once you've defined the range you're going through, don't reference ranges again inside your loop unless you want that entire loop to be affected during each journey through the loop.

So if k=0, you don't want all of the e values to be "N/A". You only want the one related to the k cell you're looking at that has a zero.

You can use offset for this. First time through the loop, k is cell c4. So, if c4=0 then e4=N/A. e4 is the same as c4.offset(0,2). That is, it's in the same row, but two columns over.

You can also use k as a reference point to help build the formula in e, so that it's not the exact same formula each time.

With that in mind, let's rewrite your code:

Dim k As Range
Dim ws As Worksheet

Set ws = ActiveSheet

For Each k In ws.Range("C4:C6")
    If k = 0 Then
        k.Offset(0, 2).Value = "N/A"
    Else
        k.Offset(0, 2).Formula = "=" & k.Offset(0, 1).Address & "/" & k.Address
    End If
Next

If you set everything in terms of k, you make sure it's all relevant, line after line.

CodePudding user response：

Alternatively

Sub demo()

  Range("E4:E6").FormulaR1C1 = _
    "=IF(RC[-2]=0,""N/A"",RC[-1]/RC[-2])"

End Sub