VBA: Change variable within loop for next contant string formula

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,274
Office Version
  1. 2016
Platform
  1. Windows
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.


Code:
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
 
Last edited:

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

ChrisGT7

Board Regular
Joined
Jul 30, 2012
Messages
87
Try this instead:
Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,351
Office Version
  1. 365
Platform
  1. Windows
You can't work with variables like that, try using an array instead.
Code:
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

Well-known Member
Joined
Nov 11, 2008
Messages
1,274
Office Version
  1. 2016
Platform
  1. Windows
Hi Chris,

That works absolutely perfect. Thank you for your time.
 

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,274
Office Version
  1. 2016
Platform
  1. Windows
Hi Norie,

That worked extremely well also. Thank you for your time.
 
Last edited:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,085
Messages
5,857,273
Members
431,867
Latest member
Dalorian

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top