VBA to replace an existing formula with new one

[i3atman]

Hi guys,

I am new to the forum and new to VBA. I am trying to write a code that would paste values of existing formula in one cell, then change an different formula in another cell and it repeat for n times. This is what I have so far:

Sub Macro1()


Macro1 Macro


For i = 6 To 29

Range("K" & i).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("BG7").Select
Application.Evaluate ("=SUMPRODUCT(Range(BA7:BF7),Range(D$7:I$7))")
Range("BG7:BG24").FillDown
Range("K" & i + 1).Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("BG7").Select
Application.Evaluate ("=SUMPRODUCT(Range(BA7:BF7),Range(D$8:I$8))")
Range("BG7:BG24").FillDown


Next i

End Sub

Any help would be highly appreciated it!

 

[i3atman]

My apologies for any confusion. The question indeed is very complex, I wish there was a way for you to see the whole worksheet.
Here is the summary of first three iterations:


Iteration 1 (when i = 7):


Calculate a SUMPRODUCT formula in cell BG7 using data in the range $D$7:$I$7
Copy down SUMPRODUCT formula down from BG7 to BG11
Copy VLOOKUP formula in K7:M7 and paste values back in K7:M7


End of iteration 1


Iteration 2 (when i = 8):


Calculate a SUMPRODUCT formula in cell BG7 using data in the range $D$8:$I$8
Copy down SUMPRODUCT formula down from BG7 to BG11
Copy VLOOKUP formula in K8:M8 and paste values back in K8:M8


End of iteration 2


Iteration 3 (when i = 9)


Calculate a SUMPRODUCT formula in cell BG7 using data in the range $D$9:$I$9
Copy down SUMPRODUCT formula down from BG7 to BG11
Copy VLOOKUP formula in K9:M9 and paste values back in K9:M9


End of iteration 3


Hope this helps. Let me know if you have any questions.
Thank you very much for your help!

I don't understand that. I showed you how to increment the range. What does your updated code look like?

Note, I often find it messy to write formulas like that where you are building the range dynamically in the middle of it. I often like to build the range, before putting into the formula, i.e.

Dim rng1 as Range
Dim rng2 as Range
Set rng1 = Range(Cells(i,"BA"),Cells(i,"BF"))
...

and then use rng1, rng2 in your formulas.

The truth is, I am finding your question very complex and confusing without having access to your spreadsheet and understanding your logic.
What may help is if you walk me through the first 3 iterations like this:

Iteration 1 (when i = 6): what do you want to ultimately happen, what EXACT formulas should go in what cells?

Then repeat for Iteration 2 (when i=7) and Iteration 3 (when i=8).
Then hopefully I will be able to detect the pattern and see what changes and what does not.