VBA Last Column but specific row for formula?

ExcelNoob222

New Member
Joined
Jun 17, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I need to put a formula in the last column of a specific row and then I need that formula to be dragged down to the last row. However I am stuck. I currently have the code:

VBA Code:
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Rows(2).Find("*", , xlValues, , xlByColumns, xlPrevious).Offset(, 1).Formula = "=SUMIFS($R$4:$R$9,$O$4:$O$9,A2)"

This will find the last column in Row 2 and place the formula there. But how do I then get that formula to drag all the way down? Thanks!
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,316
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
lastrow = Range("A" & Rows.Count).End(xlUp).Row
Cells(2, Columns.Count).End(xlToLeft).Offset(, 1).Resize(lastrow - 1).Formula = "=SUMIFS($R$4:$R$9,$O$4:$O$9,A2)"
 
Solution

ExcelNoob222

New Member
Joined
Jun 17, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
lastrow = Range("A" & Rows.Count).End(xlUp).Row
Cells(2, Columns.Count).End(xlToLeft).Offset(, 1).Resize(lastrow - 1).Formula = "=SUMIFS($R$4:$R$9,$O$4:$O$9,A2)"

Perfect! Thanks for your help.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,316
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,360
Messages
5,547,469
Members
410,797
Latest member
mlfuson
Top