VBA Paste Formula and then Drag through range

jharvey87

New Member
Joined
Nov 11, 2015
Messages
29
Office Version
  1. 2016
Platform
  1. Windows
Hi everyone,

I'm using this macro to find the next open cell in column G and paste the formula within. It works perfectly, however, I want to add the next step which would drag this formula through the entire range (stop when column A no longer has any data in it). Would someone be able to help me with this?

Sub PasteFormula()

Sheets("Viewpoint - BLog-P&P").Cells(Rows.Count, "G").End(xlUp).Offset(1). _
Value = "=SUMIFS('Revenue Spread'!$K$8:$K$3000,'Revenue Spread'!$C$8:$C$3000,""Promised/Probable"",'Revenue Spread'!$H$8:$H$3000,INDIRECT(""D""&ROW()))+SUMIFS('Revenue Spread'!$L$8:$L$3000,'Revenue Spread'!$C$8:$C$3000,""Promised/Probable"",'Revenue Spread'!$H$8:$H$3000,INDIRECT(""D""&ROW()))"

End Sub
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,154
Office Version
  1. 2013
Platform
  1. Windows
@jharvey87 Not tested but try this.

VBA Code:
Sub PasteFormula()

NextG = Sheets("Viewpoint - BLog-P&P").Cells(Rows.Count, "G").End(xlUp).Row + 1
LastA = Sheets("Viewpoint - BLog-P&P").Cells(Rows.Count, "A").End(xlUp).Row

Sheets("Viewpoint - BLog-P&P").Range("G" & NextG & ":G" & LastA). _
Formula = "=SUMIFS('Revenue Spread'!$K$8:$K$3000,'Revenue Spread'!$C$8:$C$3000,""Promised/Probable"",'Revenue Spread'!$H$8:$H$3000,INDIRECT(""D""&ROW()))+SUMIFS('Revenue Spread'!$L$8:$L$3000,'Revenue Spread'!$C$8:$C$3000,""Promised/Probable"",'Revenue Spread'!$H$8:$H$3000,INDIRECT(""D""&ROW()))"

End Sub

Hope that helps
 
Solution

jharvey87

New Member
Joined
Nov 11, 2015
Messages
29
Office Version
  1. 2016
Platform
  1. Windows
@jharvey87 Not tested but try this.

VBA Code:
Sub PasteFormula()

NextG = Sheets("Viewpoint - BLog-P&P").Cells(Rows.Count, "G").End(xlUp).Row + 1
LastA = Sheets("Viewpoint - BLog-P&P").Cells(Rows.Count, "A").End(xlUp).Row

Sheets("Viewpoint - BLog-P&P").Range("G" & NextG & ":G" & LastA). _
Formula = "=SUMIFS('Revenue Spread'!$K$8:$K$3000,'Revenue Spread'!$C$8:$C$3000,""Promised/Probable"",'Revenue Spread'!$H$8:$H$3000,INDIRECT(""D""&ROW()))+SUMIFS('Revenue Spread'!$L$8:$L$3000,'Revenue Spread'!$C$8:$C$3000,""Promised/Probable"",'Revenue Spread'!$H$8:$H$3000,INDIRECT(""D""&ROW()))"

End Sub

Hope that helps
Perfect, thank you so much!
 

jharvey87

New Member
Joined
Nov 11, 2015
Messages
29
Office Version
  1. 2016
Platform
  1. Windows
Okay, I'm struggling here. I was trying to upload an example workbook but I guess we can only do the mini-sheet feature. I'm not even sure if this is possible or too many criteria, but basically I would need to find a way to match multiple columns and one row to pull the data. Basically this spreadsheet is mapping out revenue projections but I have to match quite a few columns as some of the data is exact in columns and only one column has a difference.

Revenue Spread Example.xlsx
ABCDEFGQRSTUVWX
4LevelJob StatusJob NumberDescriptionSales DateDepartment E/R (Imported) 01/201802/201803/201804/201805/201806/201807/201808/2018
5Construction (12)Backlog1213134Construction Departments31
6Construction (12)Backlog1217044Construction Departments491
7Construction (12)Backlog1217045Construction Departments4207
8Construction (12)Backlog1217046Construction Departments0
9Construction (12)Backlog1218079Construction Departments0
10Construction (12)Backlog1218126Construction Departments0
11Construction (12)Backlog1219004Construction Departments0
12Construction (12)Backlog1219011Construction Departments0
13Construction (15)Promised/ProbableTest 1001-Design Build - Construction-#N/A
14Construction (15)Promised/ProbableTest 2001-Design Build - Construction-
15Service (15)Promised/ProbableTest 3005-Design Build Service-
16Service (15)Promised/ProbableTest 4005-Design Build Service-
17Construction (12)Backlog1219040Construction Departments0
18Construction (12)Backlog1219060Construction Departments0
19Construction (12)Backlog1219090Construction Departments0
20Construction (12)Backlog1219098Construction Departments0
21Construction (12)Backlog1219113Construction Departments0
22Construction (12)Backlog1219116Construction Departments0
23Construction (12)Backlog1219124Construction Departments0
24Construction (12)Backlog1219133Construction Departments0
25Construction (12)Backlog1219139Construction Departments0
26Service (12)Backlog1220001Service Departments0
27Construction (12)Backlog1220002Construction Departments0
28Construction (12)Backlog1220003Construction Departments0
29Service (12)Backlog1220007Service Departments0
30Construction (12)Backlog1220021Construction Departments0
31Construction (12)Backlog1220032Construction Departments0
32Construction (12)Backlog1220036Construction Departments0
33Construction (12)Backlog1220037Construction Departments0
34Service (12)Backlog1220040Service Departments0
35Construction (12)Backlog1220041Construction Departments0
36Construction (12)Backlog1220049Construction Departments0
37Construction (12)Backlog1220050Construction Departments0
38Service (12)Backlog1220052Service Departments0
39Service (12)Backlog1220061Service Departments0
Viewpoint - BLog-P&P
Cell Formulas
RangeFormula
Q13Q13=INDEX('Revenue Spread'!$K$8:$BP$15000,MATCH($B13&$C13&$D13&$F13&Q$4,'Revenue Spread'!$C$8:$C$15000&'Revenue Spread'!$E$8:$E$15000&'Revenue Spread'!$H$8:$H$15000&'Revenue Spread'!$J$8:$J$15000&'Revenue Spread'!$K$7:$BP$7,0))
G13:G16G13=SUMIFS('[Adaptive Backlog Workbook.xlsm]Revenue Spread'!$K$8:$K$3000,'[Adaptive Backlog Workbook.xlsm]Revenue Spread'!$C$8:$C$3000,"Promised/Probable",'[Adaptive Backlog Workbook.xlsm]Revenue Spread'!$H$8:$H$3000,INDIRECT("D"&ROW()))+SUMIFS('[Adaptive Backlog Workbook.xlsm]Revenue Spread'!$L$8:$L$3000,'[Adaptive Backlog Workbook.xlsm]Revenue Spread'!$C$8:$C$3000,"Promised/Probable",'[Adaptive Backlog Workbook.xlsm]Revenue Spread'!$H$8:$H$3000,INDIRECT("D"&ROW()))
Press CTRL+SHIFT+ENTER to enter array formulas.
 

jharvey87

New Member
Joined
Nov 11, 2015
Messages
29
Office Version
  1. 2016
Platform
  1. Windows
I know Q13 won't work, but anyway something similar will?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,372
Messages
5,635,880
Members
416,886
Latest member
coreyalaurence37

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