Dynamic Referencing in VBA Formula

Question

I am trying to Index/Match data only when a certain criteria is met.

I could do this with two arrays but I'm hoping there's an easy answer here.

My code is as follows:

Sub Nozeroleftbehind(lengthRow As Integer)
For i = 2 To lengthRow
    If Cells(1, i) = 0 Then Cells(1, i) = "TBD"
Next i

For i = 2 To lengthRow
    If Cells(1, i) = "#N/A" Then
        Cells(2, i) = "=INDEX(Forecast!L:L,MATCH('AA - Inbound Orders Weekly Rep'!H113,Forecast!A:A,0))"
End if
Next i

    End Sub

And then pass that sub back to the main routine.

What I am trying to get dynamic is that 'H113' cell. I can't seem to get an offset to work properly since it's already in a formula.

EDIT: Apologies, H113 moves down. Next cell would be H114.

Regards

Answer 1

Please try this code.

Sub NoZeroLeftBehind(lengthRow As Integer)
    ' 18 Oct 2017

    Dim lengthRow As Long
    Dim Tmp As Variant
    Dim C As Long

    lengthRow = 4
    For C = 2 To lengthRow
        ' bear in mind that the Cell is a Range
        ' and you want to refer to its Value & Formula property
        With Cells(1, C)
            Tmp = .Value
            ' using the Val() function will interpret a blank cell as zero value
            If Val(Tmp) = 0 Then
                .Value = "TBD"
            ElseIf IsError(Tmp) Then
                .Formula = "=INDEX(Forecast!L:L,MATCH('AA - Inbound Orders Weekly Rep'!H" & _
                           (113 + C - 2) & ",Forecast!A:A,0))"
            End If
        End With
    Next C
End Sub