Need help using SUM on 2 fields in VBA

[captainx]

Hi Again,

I may have solved the majority of my problems and the code so far seems to be a simple affair, except I cannot work out how to SUM to fileds on my SOURCE sheet and push teh result through to my output sheet.

I imagine it is a fairly basic error.

In practice I need to repeat the SUM for each month in a 12 month period, hence all the marked out lines below.

This sub will give me some drilled down data on a purchase plan for 1000 skus, the names will change, this is just to get the theory right.

This is something that I have a deadline for and a working code will save me a few hours cut n pasting formulas tomorrow.


Sub testDEMPLAN()
Dim r As Long, outputr As Long, code As String, desc As String, mth01 As Long, mth02 As Long, mth03 As Long, mth04 As Long, mth05 As Long, mth06 As Long, mth07 As Long, mth08 As Long, mth09 As Long, mth10 As Long, mth11 As Long, mth12 As Long
Dim lastrow As Long, planned As Long, firm As Long
With Sheets("SOURCE")
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
r = 2
outputr = 2
With Sheets("SOURCE")
For r = r To lastrow Step 9
code = ("A" & r)
desc = ("B" & r)
planned = r + 4
firm = r + 5
'mth01 = Sheets("SOURCE").Range("planned" & 10) + Sheets("SOURCE").Range("firm" & 10)
'This line is giving me trouble, 2 cells per month that in the first instance sit in J6+J7 that need to be combined
'The plan is to repeat for 12 columns mth01-mth12 then populate Output sheet
'Works perfectly to grab the code and desc info.

Sheets("Output").Range("A" & outputr) = .Range(code)
Sheets("Output").Range("B" & outputr) = .Range(desc)
'Sheets("Output").Range("C" & outputr) = .Range(mth01)
'Sheets("Output").Range("D" & outputr) = .Range(mth02)
'Sheets("Output").Range("E" & outputr) = .Range(mth03)
'Sheets("Output").Range("F" & outputr) = .Range(mth04)
'Sheets("Output").Range("G" & outputr) = .Range(mth05)
'Sheets("Output").Range("H" & outputr) = .Range(mth06)
'Sheets("Output").Range("I" & outputr) = .Range(mth07)
'Sheets("Output").Range("J" & outputr) = .Range(mth08)
'Sheets("Output").Range("K" & outputr) = .Range(mth09)
'Sheets("Output").Range("L" & outputr) = .Range(mth10)
'Sheets("Output").Range("M" & outputr) = .Range(mth11)
'Sheets("Output").Range("N" & outputr) = .Range(mth12)
outputr = outputr + 1
Next r
End With
End Sub


Thanks again, in case i do get a response.

 

[captainx]

Seems I have been mistaken about this forum.

 

[Padawan]

CaptainX

I'm not sure how to write your named ranges into your code. But your line of code works using the row and column labels.

mth01 = Sheets("SOURCE").Range("J6") + Sheets ("SOURCE").Range("J7")

The above line should work fine.

HTH.

 

[captainx]

Hi,

Thanks for that, but it is returning blanks for me.

The issue with coding the cells is that each product will need to be identified.

I did wonder about using an Array for the entire stock report and then adding togther 2 OFFSET fields for each month. Would that make sense?

 

[captainx]

Okay - so fixed a work around myself, which while not ideal provides the solution.

The answer?

Inserted an extra row on the SOURCE data to SUM the 2 fields in question and I appear to be able to retreive that figure for each month without any hassle.

Slight rework to original spreadsheet and I now have a clean, exportable and much less resource heavy Demand Plan.

Yay