CODEWITHSUNDEEP
vbaexcel
Ale
Ale

Reputation: 665

How to define dynamically changing column number into a formula in VBA?

I have a VBA code that calculates a formula (I know it's pretty long):

Cells(i, mcol) = "=IF(RC[-1]=""C"",(RC[-3]/SUMIFS(C[-3],C66,RC66))*(VLOOKUP('Sheet1'!RC66,GA_C!C1:C4,4,0)),SUM((RC[-3]/SUMIFS(C[-3],C66,RC66))*(VLOOKUP('Sheet1'!RC66,GA_C!C1:C4,4,0)),(RC[-3]/SUMIFS(C[-3],C66,RC66,C[-1],""GA + C""))*(VLOOKUP('Sheet1'!RC66,GA_C!C1:C3,3,0))))"

in the Vlookup it takes a 4th column in the range from C1:C4 and the 3rd column from the range C1:C3.

It was ok till the column number (4 and 3) was fixed. Now it changes each time running For cycle. Foe example, the second run column numbers will be 5 and 4, the third run 6 and 5 and so on till 12. Is there any way to integrate the column number changed dynamically into the formula above?

Thanks a lot!

I put also a whole code as well.

Sub AutoCalcV2()

Dim ws As Worksheet
Dim LastRow As Long
Dim i As Integer, n As Integer, x As Integer, j As Integer, mcol As Integer
Set ws = ActiveWorkbook.Sheets("Sheet1")
ws.Select
LastRow = Sheets("Sheet1").Range("A" & Sheets("Sheet1").Rows.Count).End(xlUp).Row

mcol = 71
For j = 1 To 11

    mcol = mcol + 1
    For i = 3 To LastRow
        On Error Resume Next
        Cells(i, mcol) = "=IF(RC[-1]=""C"",(RC[-3]/SUMIFS(C[-3],C66,RC66))*(VLOOKUP('Sheet1'!RC66,GA_C!C1:C4,4,0)),SUM((RC[-3]/SUMIFS(C[-3],C66,RC66))*(VLOOKUP('Sheet1'!RC66,GA_C!C1:C4,4,0)),(RC[-3]/SUMIFS(C[-3],C66,RC66,C[-1],""GA + C""))*(VLOOKUP('Sheet1'!RC66,GA_C!C1:C3,3,0))))"
Range("BT4").Select
    Next i
Next j
End Sub

Upvotes: 0

Views: 1142

Answers (3)

R.Katnaan
R.Katnaan

Reputation: 2526

OK, Take a look. I can give an suggestion for you. Not the whole formula, Just a part of VLOOKUP.

I know that this is your formula for cell in loop:

Cells(i, mcol) = "=IF(RC[-1]=""C"",(RC[-3]/SUMIFS(C[-3],C66,RC66))*(VLOOKUP('Sheet1'!RC66,GA_C!C1:C4,4,0)),SUM((RC[-3]/SUMIFS(C[-3],C66,RC66))*(VLOOKUP('Sheet1'!RC66,GA_C!C1:C4,4,0)),(RC[-3]/SUMIFS(C[-3],C66,RC66,C[-1],""GA + C""))*(VLOOKUP('Sheet1'!RC66,GA_C!C1:C3,3,0))))"

Now is you want to change the dynamically the column according to looping. I understand the column pair as follow:

C1:C4 & C1:C3
C1:C5 & C1:C4
C1:C6 & C1:C5
C1:C7 & C1:C6
C1:C8 & C1:C7
C1:C9 & C1:C8
C1:C10 & C1:C9
C1:C11 & C1:C10
C1:C12 & C1:C11

Actually, your looping are not clear, I can't use it. So, I used as follow:

For column = 3 To 11

    mcol = mcol + 1

    For row = 1 To lastRow

        Cells(row , mcol) = "=IF(RC[-1]=""C"",(RC[-3]/SUMIFS(C[-3],C66,RC66))*" & _
                            "(VLOOKUP('Sheet1'!RC66,GA_C!C1:C" & column + 1 & "," & column + 1 & ",0))" & _
                            ",SUM((RC[-3]/SUMIFS(C[-3],C66,RC66))*" & _
                            "(VLOOKUP('Sheet1'!RC66,GA_C!C1:C" & column + 1 & "," & column + 1 & ",0))" & _
                            ",(RC[-3]/SUMIFS(C[-3],C66,RC66,C[-1],""GA + C""))*" & _
                            "(VLOOKUP('Sheet1'!RC66,GA_C!C1:C" & column & "," & column & ",0))))"
    Next row

Next column

Try as above, it will be helpful for you.

Upvotes: 0

ManishChristian
ManishChristian

Reputation: 3784

So based on what I understood, you have 3 vlookups and you want to use 4 (4+1,5+1,6+1) for first Two vlookups and 3 (3+1,4+1,5+1) for third one.

If that so, here how you can increment your 4 and 3. 

Sub AutoCalcV2()
    Dim ws As Worksheet
    Dim LastRow As Long
    Dim i, n, x, j, mcol, iCol As Integer  '<-- Changed here
    Set ws = ActiveWorkbook.Sheets("Sheet1")
    ws.Select
    LastRow = Sheets("Sheet1").Range("A" & Sheets("Sheet1").Rows.Count).End(xlUp).Row

    mcol = 71
    iCol = 4  '<-- Newly added
    For j = 1 To 11
        mcol = mcol + 1
        For i = 3 To LastRow
            On Error Resume Next
            'Changed the formula
            Cells(i, mcol) = "=IF(RC[-1]=""C"",(RC[-3]/SUMIFS(C[-3],C66,RC66))" & _
                "*(VLOOKUP('Sheet1'!RC66,GA_C!C1:C4," & iCol & ",0)),SUM((RC[-3]/SUMIFS(C[-3],C66,RC66))" & _
                "*(VLOOKUP('Sheet1'!RC66,GA_C!C1:C4," & iCol & ",0)),(RC[-3]/SUMIFS(C[-3],C66,RC66,C[-1],""GA + C""))" & _
                "*(VLOOKUP('Sheet1'!RC66,GA_C!C1:C3," & i & ",0))))"
            Range("BT4").Select
            iCol = iCol + 1
        Next i
    Next j
End Sub

Upvotes: 0

MatthewD
MatthewD

Reputation: 6761

Dim iColumn as Integer

mcol = 71
For j = 1 To 11

    iColumn = 4
    mcol = mcol + 1

    For i = 3 To LastRow

        On Error Resume Next
        Cells(i, mcol) = "=IF(RC[-1]=""C"",(RC[-3]/SUMIFS(C[-3],C66,RC66))*(VLOOKUP('Sheet1'!RC66,GA_C!C1:C4," & str(iColumn) & ",0)),SUM((RC[-3]/SUMIFS(C[-3],C66,RC66))*(VLOOKUP('Sheet1'!RC66,GA_C!C1:C4," & str(iColumn) & ",0)),(RC[-3]/SUMIFS(C[-3],C66,RC66,C[-1],""GA + C""))*(VLOOKUP('Sheet1'!RC66,GA_C!C1:C3,3,0))))"
        Range("BT4").Select

        iColumn = iColumn + 1

    Next i
Next j

Upvotes: 1

Related Questions