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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,520
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,520
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,646
Messages
5,549,171
Members
410,903
Latest member
natesreich
Top