Solved: Help with VBA

[NewDaddy]

Hi All

Need some assistance again please.

I am putting together new format budget report using VBA and I am not sure how to tackle putting in formulas which calculate the difference between 1. Budget manager?s projection and the full year budget, and 2 subtotals each spend class / totals the cost centre.

I think this is quite complicated to explain so I have attached an example to illustrate what I need and highlighted the range. Please ignore the headings in row 1 as these are out of line.

Cheers
Jay

[gnod]

sorry, but i don't understand what do you want..

[NewDaddy]

sorry, but i don't understand what do you want..

Sorry, I thought I had explained it.

I will send out the attached report to budget holders who will view and will then put in their projected outturn for each code (column O). They will then email the sheet back to me to collate.

So it would be better if I could get the forumulas in before I send the sheet out so as the person fills in their projected spend they can see the totals for each 'class' of spend and its relative variance against the budget (plus the fact that they wouldn't be able to!!!!)

I need code that will put in the required formulas in column O (I can put in the formula for column P as this doesn't change and is for all rows) but I am not sure how to put in the "totalling" formula in column O. I am able to put a formula in for each row containing 'Total Class' but not sure how to refer to the range to sum, if that makes sence.
The formulas required are in the attached example to show what I am after.

Basically, at each occurance of 'Total Class' in column B a subtotal of the lines above needs to be put into column O.

Example

Column B (amounts in column O of corresponding row)

M0001
M0002
M0003
Total Class (M)
N0001
N0002
N0003
N0004
Total Class (N)
R1000
Total Class (R)

I hope I have explained this better? and am not comming across as condesending.

Cheers
Jay

[Bob Phillips]

It is very difficult for us to understand what you are meaning if you don't even botehr to get the headings in line.

Can't you sort it out, and post it with some examples of what you want?

[NewDaddy]

It is very difficult for us to understand what you are meaning if you don't even botehr to get the headings in line.

Can't you sort it out, and post it with some examples of what you want?

Sorry, it's not that I am not bothered I didnt think it made any difference as it doesn't relate/affect what I need help with.

Anyway I have attached an example of the 'final product'
I already have put together code for the splitting, formatting etc but need help with sum formula in column O.

Again sorry

Cheers

[CodeMakr]

http://www.vbaexpress.com/kb/getarticle.php?kb_id=115

Not sure if this is what you are asking for, but take a look to see if it will work.

[Bob Phillips]

I'm not getting it, what gets calculated in N6 for instance.

[austenr]

Just a wid guess here () but I thinkist he means a total for the budget columns. Course with so little info to go on I might be wrong.

[NewDaddy]

Just a wid guess here () but I thinkist he means a total for the budget columns. Course with so little info to go on I might be wrong.

Hi All

I am really sorry, I am obviously not explaining this at all. I will go through it in detail.

Column A to L (on revised example uploaded) is raw data downloaded from our system. There are no formulas in this part, and none required.
I then use VBA to split each cost centre into its own sheet and then correct the headings add 2 columns on the end, Bgt Mgr Projection (N) and Variance (O), and format the report.

Budget managers receive a printed version, via snail main, of this to review their spend / income on their cost centre(s).

What I am trying to do is send them the same report (as they are familiar with its layout etc) electronically so they can email it back to me to 'report' their projected total spend for each account code in column A, (i.e. M5000) for the year.
They do this by putting their projection in column N for each of the account codes (M5000, N0100, N0200 etc).
For example for N0100 they would put in, say 4,042,277 in N6 if they expected to 'come in on line' with the budget. They would then put in a projection in N7 for N0200 and so on.
What I need help with is to put in the formulas that total each 'class' (i.e. M, N etc) and a total spend of all 'classes', which in my example for this cost centre is as follows;

Total Class M (N5)
Which adds together (N4)

Total Class N (N9)
Which adds together (N6:N8)

Total Class Q (N11)
Which adds together (N10)

Total Class R (N13)
Which adds together (N12)

And finally
Total Cost Centre (N14)
Which adds together (N5;N9;N11;N13)

The variance formula in column O I would be able to do, but for explanation purposes this is Full Year Budget (Column J) less Bgt Mgr Projection (Column N) for each line of the report. This reports by how much, if any, they are under/over spending against their budget.

I am not sure how to tackle putting in the formulas for column N as all the ranges are different, and are constantly changing and an added complication is that there are no values in column N (on the respective account code lines) to sum.

Again I am truly sorry for such bad communication and there is no excuse as I should have explained it better, and I hope I have now done this.

Anyway, it doesn?t hurt to say it, but your help and patience is really appreciated.

Cheers
J

[NewDaddy]

http://www.vbaexpress.com/kb/getarticle.php?kb_id=115

Not sure if this is what you are asking for, but take a look to see if it will work.

Hi CodeMakr

Thanks for the suggestion but not quite what I am looking for.

Cheers
J

[JimmyTheHand]

Maybe this one?
[vba]Sub AddFormula()
Dim c1 As Range, AccCode As Range, Rng As Range
Dim FRML As String

Set Rng = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each c1 In Rng.Cells
If c1 = "Total Class" Then
FRML = "=SUM("
For Each AccCode In Rng.Cells
If Left(AccCode, 1) = c1.Offset(, 1) Then FRML = FRML & AccCode.Offset(, 13).Address & ","
Next AccCode
FRML = Left(FRML, Len(FRML) - 1) & ")"
c1.Offset(, 13).Formula = FRML
End If
If c1 = "Total Cost Centre" Then
FRML = "=SUM("
For Each AccCode In Rng.Cells
If AccCode = "Total Class" Then FRML = FRML & AccCode.Offset(, 13).Address & ","
Next AccCode
FRML = Left(FRML, Len(FRML) - 1) & ")"
c1.Offset(, 13).Formula = FRML
End If
Next c1
End Sub[/vba]

[NewDaddy]

Hi Jimmy

Excellent, Thank you very much.
I have tested it and it does exactly what I need.

Thanks a million, i'll learn a few things from this aswel.

Cheers
Jay

Also thanks to everyone and you all have great Christmas and New Year.

[gnod]

can you attach the finish file coz when i add the code of JimmyTheHand in the standard module, nothing happens.. it doesn't create the formula..