VBA code for cycling down through rows and update column based on fixed cell values

Sixaside

New Member
Joined
Apr 13, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hi, I'm sure this is simple but I haven't been able to modify any VBA code I've found to do what I want. Hopefully someone can help.

What I need to do is copy the values in A2:C2 to G2:I2, and then copy the value of M2(value, not the formula) to D2. Then I want to move onto copying A3:C3 to G2:I2 and then copy the value of M2 to D3. So basically cycle down through columns A:C until there is no more data in those columns and each time copy the values of the new row to G2:I2 and copy E2 to column D for the row in question. Columns E and G:I will only ever be populated in row 2 at any time. Screenshot included. Any help much appreciated.

1618353336229.png
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Hi,​
for efficiency why not just applying a formula directly in column D rather than using cell M2 ❓
 
Upvote 0
Hi,​
for efficiency why not just applying a formula directly in column D rather than using cell M2 ❓
Thanks for the reply. I just realised that there's an error in my question. Where I reference M2, that should read E2. Elsewhere, where I reference E2, that is correct. Anyway E2 is a calculation based on a matrix and dragging the formula won't work. The matrix changes depending on the values in G2:I2. In the example the matrix would be 3x3 but that can change also.
 
Upvote 0
So you forgot to describe your formula in E2 but to avoid a NES (*) it could be easier with an attachment …​
(*) Never Ending Story
 
Upvote 0
Will elaborate when I have time tomorrow. Don't think the formula in E2 matters too much as I only want to take the value from that cell regardless of how that value was arrived at.
 
Upvote 0
How about
VBA Code:
Sub MM1()
Range("A2:C" & Cells(Rows.Count, "A").End(xlUp).Row).Copy Range("G2")
Range("D:D").Value = Range("E:E").Value
End Sub
 
Upvote 0
I assume you have a formula in M2 that takes the values in G2:I2. Is there a reason not to put that formula into E2 or into D2:D? as suggested above? It appears from your description M2 simply serves as a formula holder for the G2:I2 values and E2 is merely an intermediate column to get the M2 value into column D. All this could be done with formulas without resorting to VBA (although I very much like to do VBA :)
 
Upvote 0
And as VBA can evaluate the same formula so more efficient than looping …​
 
Upvote 0
Hi. Thanks for the feedback. I have an excel file I was going to upload but can't see an option to do that so I'll post another image here that might explain what I want to do more clearly. Any help appreciated!

1618437377184.png
1618437377184.png
 
Upvote 0
Sorry. Forgot to explain what's happening in L2. The formula in L2 returns the maximum value in column E where the corresponding values in H:J are all greater than zero.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

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