VBA Dynamic Sum

[Littlemalky]

Here's the situation,

I'm using VBA to to paste three columns with a dynamic amount of rows being partnumbers, descriptions, and quantity. I have it xlDown and offset one row to sum that quantity, then I have it offset two rows down to paste another set of data below that, in which; again, I xlDown and offset and sum that portion of the quantities. However, below this second portion of quantities I need to create a "Grand Total" line right below that last quantity that sums the two totals on the page.

If it helps, with the first total, the cell directly to the left of it is entitled, "Total Raw Materials", and the other total is entitled, "Total In-House Blanks". Since all of this is dynamic including the placement of the "Grand Total" line, how do I search for the cell directly to the right of these titles and add it to my grand total sum formula in the cell right below the last total?

 

[JackDanIce]

You'll need to modify the following:

Cells.Find(what:="Total Raw Materials", after:=Range("A1")).Offset(0,1).Select

 

[Littlemalky]

What's the after Range A1 part? What is that representing so I know how to adjust it?

 

[lenze]

This should get you started

Sub LocateSums()
Dim C As Range
Dim D As Range
Dim x As Variant
Set C = Range("A:A").Find("Total Raw Materials", LookIn:=xlValues)
Set D = Range("A:A").Find("Total In-House Blanks", LookIn:=xlValues)
x = C.Value + D.Value
End Sub

lenze

 

[Littlemalky]

Thanks for the help lenze, I have a followup question for you: once the x is determined how do I state that in a formula within the activecell?

Something like?

ActiveCell.ForumulaR1C1 = x

 

[lenze]

I overloked something when I posted.
You will need to use offset to add the totals. Something like this

x = C.Offset(0,1).Value + D.Offset(0,1).Value

All you need is

ActiveCell = x

lenze

 

[Littlemalky]

It works perfectly! Thank you so much lenze!