VBA: Change variable within loop for next contant string formula

[FryGirl]

I have about 10 Const as String formulas and need to loop thru them column by column. It starts with column 3.

The part in Red, need to change in the loop, but can't get the right syntax to change the last digit of the sFormula to grab the next formula for the next column.

Sub dkdk()

    Const sFormula3     As String = "=My_a_Value"
    Const sFormula4     As String = "=(COUNTIFS(Data!$T$2:$T$638675,$B2,Data!P$2:P$638675,D$1)/60)*1.2138"
    Const sFormula5 etc....

    Dim i As Long

    With Sheet7     
   
        For i = 3 To 12
            .Range(Cells(2, i), Cells(LR, i)).Formula = [COLOR=#ff0000]sFormula1[/COLOR]
            .Columns(i).Value = .Columns(i).Value
        Next i

    End With

End Sub

I tried sFormula & i, but that failed

 

[ChrisGT7]

Try this instead:

Option Base 1


Sub dkdk()
    Dim Arr(10, 1) As String
    Arr(1, 1) = "=My_a_Value"
    Arr(2, 1) = "=(COUNTIFS(Data!$T$2:$T$638675,$B2,Data!P$2:P$638675,D$1)/60)*1.2138"
    Arr(3, 1) = etc....


    With Sheet7
   
        For i = 3 To 12
            .Range(Cells(2, i), Cells(LR, i)).Formula = Arr(i - 2, 1)
            .Columns(i).Value = .Columns(i).Value
        Next i


    End With


End Sub

 

[Norie]

You can't work with variables like that, try using an array instead.

Sub dkdk()
Dim i As Long
Dim sFormulas As Variant

    
    Const sFormula3     As String = "=My_a_Value"
    Const sFormula4     As String = "=(COUNTIFS(Data!$T$2:$T$638675,$B2,Data!P$2:P$638675,D$1)/60)*1.2138"

    sFormulas = Array(sformula2, sFormula4) ' add the other constants

    With Sheet7
   
        For i = 3 To 12
            .Range(Cells(2, i), Cells(LR, i)).Formula = sFormulas(i - 3)
            .Columns(i).Value = .Columns(i).Value
        Next i

    End With

End Sub

 

[FryGirl]

Hi Chris,

That works absolutely perfect. Thank you for your time.

 

[FryGirl]

Hi Norie,

That worked extremely well also. Thank you for your time.