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!

 

[Joe4]

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.

 

[i3atman]

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.

 

[Joe4]

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.

 

[i3atman]

Hi Joe4,

Here is the sample of the data:

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

<tbody>
</tbody>

Let me know if you need anything else.

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.

 

[Joe4]

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?

 

[i3atman]

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.

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?

 

[Joe4]

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.

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.

[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"

 

[i3atman]

I tried your suggestion, however the result is the same. Here is more detailed data set that I am working with and more explanation:

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

<colgroup><col><col><col span="6"><col><col span="4"><col><col span="5"><col><col><col span="2"></colgroup><tbody>
</tbody>

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.

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.

[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"

 

[Joe4]

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.

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.