View Full Version : Solved: Help with VBA

NewDaddy

12-19-2006, 07:30 AM

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

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

NewDaddy

12-19-2006, 08:38 AM

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

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

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

12-19-2006, 08:47 AM

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

12-19-2006, 01:33 PM

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.

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

austenr

12-19-2006, 03:53 PM

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

NewDaddy

12-20-2006, 01:22 AM

Just a wid guess here (:devil2:) 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

12-20-2006, 01:26 AM

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

12-20-2006, 02:18 AM

Maybe this one?

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

NewDaddy

12-20-2006, 03:32 AM

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.

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..

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.