VBA: Change variable within loop for next contant string formula

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,225
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:

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

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,218
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,225
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,225
Office Version
  1. 2016
Platform
  1. Windows
Hi Norie,

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

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,913
Members
414,110
Latest member
docops

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