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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,625
Office Version
  1. 2010
Platform
  1. Windows
Hi,​
for efficiency why not just applying a formula directly in column D rather than using cell M2 ❓
 

Sixaside

New Member
Joined
Apr 13, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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.
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,625
Office Version
  1. 2010
Platform
  1. Windows
So you forgot to describe your formula in E2 but to avoid a NES (*) it could be easier with an attachment …​
(*) Never Ending Story
 

Sixaside

New Member
Joined
Apr 13, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,022
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
 

vw412

Active Member
Joined
Dec 16, 2011
Messages
343
Office Version
  1. 2019
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows

ADVERTISEMENT

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 :)
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,625
Office Version
  1. 2010
Platform
  1. Windows
And as VBA can evaluate the same formula so more efficient than looping …​
 

Sixaside

New Member
Joined
Apr 13, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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
 

Sixaside

New Member
Joined
Apr 13, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,144,611
Messages
5,725,311
Members
422,610
Latest member
sanantonio

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
Top