Excel Help, Please

Factual Questions
1

Photoshop I know inside and out, but Excel is kicking my butt.

Can anybody help me with this scenario?:

I have a spreadsheet that I use for billing statements. One of the columns is called “Billed to Date” (“E”) and the next column is called “Billed this Period” (“F”). Each time I make up a new statement I have to add each value in “F” to the corresponding value in “E”, and enter the new “F” values. There are about thirty values, so it’s a real drag.

What I’d like to do is write a macro that I could run that would A. Do the addition and change the values in “E” and B.Change the values in “F” to 0.00 -ready for this period’s entries.

Any help would be greatly appreciated. I would gladly trade Photoshop knowledge if anyone needs it.

Thanks.

2

Unless I’m misreading your request, this should help:

http://www.rci.rutgers.edu/~lreed/barney.xls

No zero in the F column, sorry.

3

No idea sorry I hate excel

4

If I’m understanding your question, you want to update your statement at month end, by carrying the Billed To Date totals forward, and setting the Billed This Period totals to zero.

The trick is that you have to store the Billed To Date values somewhere else (either in another sheet, or in a table on the same sheet). Your Column E is calculated using these values and your Billed This Period values. For example:

Assume that you store the Billed To Date values in Column S. For Row 3, you would enter the following formula in Cell E3:

=F3+S3

At month end, your macro should copy the values in Column E to Column S, and set the value of Column F to zero. The relevant VBA code is as follows:

Range(“F3”).Select
Selection.Copy

Range(“S3”).Select
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False

Range(“F3”).Value = 0

Of course, this doesn’t have to be done one cell at a time; you can do an entire range at once (e.g., F3:F30).

So, if you have a customer who has been billed 500.00 in prior periods, and 50.00 this period (Column F), Column E will display 550.00 (S3 + F3). At month end, the macro will copy the 550.00 to S3, and anything you enter in F3 will be added to this value, and displayed in E3.

I hope this makes sense.

5

brachyrhynchos:
Thanks for the attempt,but when I call up your web page I just get one line of assorted weird symbols.

splatterpunk:
Perfect. Exactly what I am trying to do.

Thank you both for taking the time. The SDMB rocks!

6

The sample code should read:

Range(“E3”).Select
Selection.Copy

Range(“S3”).Select
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False

Range(“F3”).Value = 0

Also, upon re-reading the OP, I think brach’s solution might be more suitable for customer statements. But then, what do I know? I’m only an accountant.

Anyway, good luck!