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

Please clarify: what would H113 look like as the loop progresses if it were "dynamic"? I113, J113 or H114, H115...? — Excelosaurus
– Excelosaurus, Commented Oct 17, 2017 at 23:44

Variatus · Accepted Answer · 2017-10-18 02:53:43Z

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

answered Oct 18, 2017 at 2:53

Variatus

14.4k2 gold badges16 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Reece Edwards Over a year ago

Thank you! Had to change it slightly but other than, it worked perfectly! Much appreciated.

Excelosaurus · Accepted Answer · 2017-10-18 00:02:13Z

0

Knowing that you want to go H113, H114:

Cells(2, i) = "=INDEX(Forecast!L:L,MATCH('AA - Inbound Orders Weekly Rep'!H" & CStr(111 + i) & ",Forecast!A:A,0))"

answered Oct 18, 2017 at 0:02

Excelosaurus

2,8591 gold badge17 silver badges20 bronze badges

Collectives™ on Stack Overflow

Dynamic Referencing in VBA Formula

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related