VBA to replace an existing formula with new one

I

i3atman

New Member
Joined
Feb 6, 2017
Messages
6
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!
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
Welcome to the Board!

Rather than trying to decipher VBA code that may have issues with it, can you just explain all the details of what you currently have and want to have in plain English?
And include what your formulas should look like.
 
Upvote 0
Thank you very much! I have 24 rows of data that contain a vlookup function (K6:K29), which is looking up the highest value out of 18 rows of data that contain SUMPRODUCT function (BG7:BG24). SUMPRODUCT function ranks each 18 rows of data based on weights that are located in rows D6:I6, D7:I7, D8:I8 ... D29:I29. My goal is to write a code that would calculate the highest rank for first row (D6:I6), copy + paste result as a value in cell (K6), then do the same thing for all remaining rows. I tried recording the macro, which worked however if my data set changes, I would have to constantly edit macro. Let me know if that makes sense.
 
Upvote 0
I am having a hard-time visualizing this (mostly because I cannot see what your data looks like).
Any chance you could post some images of your data (before and after)?

You cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.
 
Upvote 0
Hi Joe4,

Here is the sample of the data:

[TABLE="width: 1284"]
<tbody>[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD][/TD]
[TD][/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Category[/TD]
[TD]Weight1[/TD]
[TD]Weight2[/TD]
[TD]Weight3[/TD]
[TD]Weight4[/TD]
[TD]Weight5[/TD]
[TD]Weight 6[/TD]
[TD]Rank1[/TD]
[TD]Rank2[/TD]
[TD]Rank3[/TD]
[TD][/TD]
[TD][/TD]
[TD]Weight1[/TD]
[TD]Weight2[/TD]
[TD]Weight3[/TD]
[TD]Weight4[/TD]
[TD]Weight5[/TD]
[TD]Weight 6[/TD]
[TD]Rank[/TD]
[TD]Player[/TD]
[/TR]
[TR]
[TD]Points[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]36%[/TD]
[TD]0%[/TD]
[TD]64%[/TD]
[TD]Player2[/TD]
[TD]Player4[/TD]
[TD]Player5[/TD]
[TD][/TD]
[TD][/TD]
[TD]9.6[/TD]
[TD]13.2[/TD]
[TD]16.7[/TD]
[TD]9.1[/TD]
[TD]16.5[/TD]
[TD]16.5[/TD]
[TD]4[/TD]
[TD]Player1[/TD]
[/TR]
[TR]
[TD]Rebounds[/TD]
[TD]10%[/TD]
[TD]4%[/TD]
[TD]1%[/TD]
[TD]35%[/TD]
[TD]4%[/TD]
[TD]47%[/TD]
[TD]Player2[/TD]
[TD]Player4[/TD]
[TD]Player5[/TD]
[TD][/TD]
[TD][/TD]
[TD]5.8[/TD]
[TD]8.3[/TD]
[TD]8.3[/TD]
[TD]8.3[/TD]
[TD]8.4[/TD]
[TD]8.3[/TD]
[TD]1[/TD]
[TD]Player2[/TD]
[/TR]
[TR]
[TD]Blocks[/TD]
[TD]0%[/TD]
[TD]1%[/TD]
[TD]0%[/TD]
[TD]33%[/TD]
[TD]0%[/TD]
[TD]66%[/TD]
[TD]Player2[/TD]
[TD]Player4[/TD]
[TD]Player5[/TD]
[TD][/TD]
[TD][/TD]
[TD]19.9[/TD]
[TD]19.7[/TD]
[TD]19.7[/TD]
[TD]19.7[/TD]
[TD]19.7[/TD]
[TD]19.6[/TD]
[TD]5[/TD]
[TD]Player3[/TD]
[/TR]
[TR]
[TD]Steals[/TD]
[TD]4%[/TD]
[TD]5%[/TD]
[TD]12%[/TD]
[TD]34%[/TD]
[TD]4%[/TD]
[TD]40%[/TD]
[TD]Player2[/TD]
[TD]Player4[/TD]
[TD]Player5[/TD]
[TD][/TD]
[TD][/TD]
[TD]19.1[/TD]
[TD]8.8[/TD]
[TD]8.9[/TD]
[TD]8.9[/TD]
[TD]8.8[/TD]
[TD]8.8[/TD]
[TD]2[/TD]
[TD]Player4[/TD]
[/TR]
[TR]
[TD]FGs[/TD]
[TD]7%[/TD]
[TD]1%[/TD]
[TD]14%[/TD]
[TD]22%[/TD]
[TD]5%[/TD]
[TD]51%[/TD]
[TD]Player2[/TD]
[TD]Player4[/TD]
[TD]Player5[/TD]
[TD][/TD]
[TD][/TD]
[TD]12.9[/TD]
[TD]14.4[/TD]
[TD]14.4[/TD]
[TD]14.4[/TD]
[TD]14.4[/TD]
[TD]14.4[/TD]
[TD]3[/TD]
[TD]Player5[/TD]
[/TR]
[TR]
[TD]Turnovers[/TD]
[TD]6%[/TD]
[TD]7%[/TD]
[TD]8%[/TD]
[TD]31%[/TD]
[TD]7%[/TD]
[TD]41%[/TD]
[TD]Player2[/TD]
[TD]Player4[/TD]
[TD]Player5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Assists[/TD]
[TD]13%[/TD]
[TD]7%[/TD]
[TD]0%[/TD]
[TD]16%[/TD]
[TD]9%[/TD]
[TD]54%[/TD]
[TD]Player2[/TD]
[TD]Player4[/TD]
[TD]Player5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Let me know if you need anything else.




Joe4 said:
I am having a hard-time visualizing this (mostly because I cannot see what your data looks like).
Any chance you could post some images of your data (before and after)?

You cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.
Click to expand...
 
Upvote 0
OK, unfortunately I think that actually leads to more questions than answers (I am not sure what I am looking at there). I am guessing that there are many formulas involved, and you haven't even gotten to column BG yet and the expected output.

So, since I doubt I am going to be able to fully grasp this without having access to the data, all formulas, logic, and expected results, let's try and take a different approach.
In your original question, it sounded like you might have code that does what you want, just not for all cells (it needs to repeat). So let's go with the theory that your code is working to some extent, and just needs to be expanded.
Is your code one complete iteration of what needs to happen in a single loop, or did you try to start a second or third also?
What part of your original code needs to be generalized?
 
Upvote 0
My code has two iterations of what needs to happen. If I would break it down into one it would look like this:

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


I am having issue with this part of the code:

Application.Evaluate ("=SUMPRODUCT(Range(BA7:BF7),Range(D$7:I$7))") - Here I am asking to change from initial Range(D$6:I$6) to Range(D$7:I$7), then calculate it. Currently the code only calculates with original Range(D$6:I$6). Let me know if that makes sense.



Joe4 said:
OK, unfortunately I think that actually leads to more questions than answers (I am not sure what I am looking at there). I am guessing that there are many formulas involved, and you haven't even gotten to column BG yet and the expected output.

So, since I doubt I am going to be able to fully grasp this without having access to the data, all formulas, logic, and expected results, let's try and take a different approach.
In your original question, it sounded like you might have code that does what you want, just not for all cells (it needs to repeat). So let's go with the theory that your code is working to some extent, and just needs to be expanded.
Is your code one complete iteration of what needs to happen in a single loop, or did you try to start a second or third also?
What part of your original code needs to be generalized?
Click to expand...
 
Upvote 0
I am having issue with this part of the code:

Application.Evaluate ("=SUMPRODUCT(Range(BA7:BF7),Range(D$7:I$7))") - Here I am asking to change from initial Range(D$6:I$6) to Range(D$7:I$7), then calculate it. Currently the code only calculates with original Range(D$6:I$6). Let me know if that makes sense.
Click to expand...
Not so much.
What determines what that range should be?
Is it supposed to be incrementing by one row each time?
If so, maybe you can incorporate "i" into your equation, i.e.
Code: 
[COLOR=#574123]Application.Evaluate ("=SUMPRODUCT(Range(BA" & i & ":BF" & i & "),Range(D$" & i & ":I$" & i & "))")
[/COLOR]
If it needs to be one more than i, then change all references to "i" in the formula above to "i+1"
 
Upvote 0
I tried your suggestion, however the result is the same. Here is more detailed data set that I am working with and more explanation:


[TABLE="width: 1427"]
<colgroup><col><col><col span="6"><col><col span="4"><col><col span="5"><col><col><col span="2"></colgroup><tbody>[TR]
[TD]3[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]BA[/TD]
[TD]BB[/TD]
[TD]BC[/TD]
[TD]BD[/TD]
[TD]BE[/TD]
[TD]BF[/TD]
[TD]BG[/TD]
[TD]BH[/TD]
[TD]BI[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Category[/TD]
[TD]Weight1[/TD]
[TD]Weight2[/TD]
[TD]Weight3[/TD]
[TD]Weight4[/TD]
[TD]Weight5[/TD]
[TD]Weight 6[/TD]
[TD] [/TD]
[TD]Rank1[/TD]
[TD]Rank2[/TD]
[TD]Rank3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Weight1[/TD]
[TD]Weight2[/TD]
[TD]Weight3[/TD]
[TD]Weight4[/TD]
[TD]Weight5[/TD]
[TD]Weight 6[/TD]
[TD]Combined[/TD]
[TD]Rank[/TD]
[TD]Player[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Points[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]36%[/TD]
[TD]0%[/TD]
[TD]64%[/TD]
[TD] [/TD]
[TD]Player2[/TD]
[TD]Player4[/TD]
[TD]Player5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]9.6[/TD]
[TD]13.2[/TD]
[TD]16.7[/TD]
[TD]9.1[/TD]
[TD]16.5[/TD]
[TD]16.5[/TD]
[TD]16.5[/TD]
[TD]4[/TD]
[TD]Player1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Rebounds[/TD]
[TD]10%[/TD]
[TD]4%[/TD]
[TD]1%[/TD]
[TD]35%[/TD]
[TD]4%[/TD]
[TD]47%[/TD]
[TD] [/TD]
[TD]Player2[/TD]
[TD]Player4[/TD]
[TD]Player5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5.8[/TD]
[TD]8.3[/TD]
[TD]8.3[/TD]
[TD]8.3[/TD]
[TD]8.4[/TD]
[TD]8.3[/TD]
[TD]8.3[/TD]
[TD]1[/TD]
[TD]Player2[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Blocks[/TD]
[TD]0%[/TD]
[TD]1%[/TD]
[TD]0%[/TD]
[TD]33%[/TD]
[TD]0%[/TD]
[TD]66%[/TD]
[TD] [/TD]
[TD]Player2[/TD]
[TD]Player4[/TD]
[TD]Player5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]19.9[/TD]
[TD]19.7[/TD]
[TD]19.7[/TD]
[TD]19.7[/TD]
[TD]19.7[/TD]
[TD]19.6[/TD]
[TD]19.6[/TD]
[TD]5[/TD]
[TD]Player3[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Steals[/TD]
[TD]4%[/TD]
[TD]5%[/TD]
[TD]12%[/TD]
[TD]34%[/TD]
[TD]4%[/TD]
[TD]40%[/TD]
[TD] [/TD]
[TD]Player2[/TD]
[TD]Player4[/TD]
[TD]Player5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]19.1[/TD]
[TD]8.8[/TD]
[TD]8.9[/TD]
[TD]8.9[/TD]
[TD]8.8[/TD]
[TD]8.8[/TD]
[TD]8.8[/TD]
[TD]2[/TD]
[TD]Player4[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]FGs[/TD]
[TD]7%[/TD]
[TD]1%[/TD]
[TD]14%[/TD]
[TD]22%[/TD]
[TD]5%[/TD]
[TD]51%[/TD]
[TD] [/TD]
[TD]Player2[/TD]
[TD]Player4[/TD]
[TD]Player5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]12.9[/TD]
[TD]14.4[/TD]
[TD]14.4[/TD]
[TD]14.4[/TD]
[TD]14.4[/TD]
[TD]14.4[/TD]
[TD]14.4[/TD]
[TD]3[/TD]
[TD]Player5[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Turnovers[/TD]
[TD]6%[/TD]
[TD]7%[/TD]
[TD]8%[/TD]
[TD]31%[/TD]
[TD]7%[/TD]
[TD]41%[/TD]
[TD] [/TD]
[TD]Player2[/TD]
[TD]Player4[/TD]
[TD]Player5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Assists[/TD]
[TD]13%[/TD]
[TD]7%[/TD]
[TD]0%[/TD]
[TD]16%[/TD]
[TD]9%[/TD]
[TD]54%[/TD]
[TD] [/TD]
[TD]Player2[/TD]
[TD]Player4[/TD]
[TD]Player5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]


Cells K7:M13 = VLOOKUP(K$5,$BH$7:$BI$11,2) ----- looking up top 3 players ranked for each category (Points, Rebounds, etc)
Cells BG7:BG11 = SUMPRODUCT (AZ7:BG7.$D$7:$I$7) ----- calculates a combined score for each Weight
Cells BH7:BH11 =RANK.EQ(BG7,$BG$7:$BG$11,1) ----- ranks all five players based on a combined score

Steps to write a code:
1. Indicate the range of data array for given categories (Points, Rebounds, etc) ---- For i = 7 To 13

First iteration:
1. Calculate a combined score (Cell BG7) for first category (Points) using Weights in the range ($D$7:$I$7) --- Using SUMPRODUCT formula listed above
2. Fill down formula from BG7 to BG13
3. Copy and paste values of existing VLOOKUP function in cells K7:M7
End of first iteration

Second iteration:
1. Calculate a combined score (Cell BG7) for first category (Points) using Weights in the range ($D$8:$I$8) --- Using SUMPRODUCT formula listed above
2. Fill down formula from BG7 to BG13
3. Copy and paste values of existing VLOOKUP function in cells K8:M8
End of second iteration

The code would continue based on indicated data range (7 to 13)

The issue I am having is that SUMPRODUCT formula keeps the same range ($D$7:$I$7), instead of increasing by one during each iteration. Hope this helps.






Joe4 said:
Not so much.
What determines what that range should be?
Is it supposed to be incrementing by one row each time?
If so, maybe you can incorporate "i" into your equation, i.e.
Code: 
[COLOR=#574123]Application.Evaluate ("=SUMPRODUCT(Range(BA" & i & ":BF" & i & "),Range(D$" & i & ":I$" & i & "))")
[/COLOR]
If it needs to be one more than i, then change all references to "i" in the formula above to "i+1"
Click to expand...
 
Upvote 0
The issue I am having is that SUMPRODUCT formula keeps the same range ($D$7:$I$7), instead of increasing by one during each iteration. Hope this helps.
Click to expand...
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.
Code: 
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.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
238
willow1985
willow1985
E
  • Solved
macro for copying values to the next blank cell
Replies
5
Views
356
Micron
M
C
  • Question Question
Please help clean up my convoluted VBA code. It seems very redundant and repetitive.
Replies
1
Views
414
myall_blues
myall_blues
K
  • Question Question
Increasing row before the macro runs again
Replies
1
Views
182
kbk
K
N
  • Question Question
Adding to an existing MACRO
Replies
2
Views
97
Northern NY Design
N

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

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
Back
Top