Macros in Excel

[Diane Frost]

Can someone please help me figure out how to perform the
following in Excel:
The spreadsheet we are working with is a cash report. We
are in the process of recreating the spreadsheet in
Excel. We currently have the spreadsheet in Quattro Pro.
When we open the cash report in QP, we run a macro that
takes the month to date totals and rolls them into the
prior balance for that day. Here's what we need excel to
calculate:
We'll use little numbers for the sake of explaining what
we need.
We have a figure in B5 = $100.00
we need to add B5, B6 and B7 which would create B8 as the
sum of those. Then we need to take B8 and move it to B5
to replace the prior day's figure. All we can't figure
out is the step of the macro taking the figure from B8 and
replacing B5....without getting a circular
reference...it's tells us circular reference instead of
our figure.
Any help would be much appreciated....

[Bob Kilmer]

Sub Rollem()
Dim lSum As Long
With Application.WorksheetFunction
Range("B8") = .Sum(Range("B5:B7"))
End With
Range("B5") = Range("B8")
End Sub

"Diane Frost" <dfr...@pcblawfirm.com> wrote in message
news:060401c2fed5$6c5b09f0$a001...@phx.gbl...

[Bob Kilmer]

Or just:

Sub Rollem()
Dim lSum As Long
With Application.WorksheetFunction

Range("B5").Value = .Sum(Range("B5:B7"))
End With
End Sub

"Diane Frost" <dfr...@pcblawfirm.com> wrote in message
news:060401c2fed5$6c5b09f0$a001...@phx.gbl...

[Diane Frost]

Bob Kilmer, Thanks for the coding. I used this one:

Sub Rollem()
Dim lSum As Long
With Application.WorksheetFunction
Range("B8") = .Sum(Range("B5:B7"))
End With
Range("B5") = Range("B8")
End Sub

My next question is... I need to change this macro to do the same steps
for 3 other sections of my spreadsheet. How do I continue within this
macro to repeat those steps for other cell ranges? Thanks for your help,
Diane

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Bob Kilmer]

One way is to use the programmer's secret weapon - cut, paste, modify! If
things stay simple, it not too bad an alternative.

Sub RollC()
With Application.WorksheetFunction
Range("C8") = .Sum(Range("C5:C7"))
End With
Range("C5") = Range("C8")
End Sub

Sub RollD()
With Application.WorksheetFunction
Range("D8") = .Sum(Range("D5:D7"))
End With
Range("D5") = Range("D8")
End Sub

(You may notice "Dim lSum As Long" is missing. It is unneeded.)

BTW, these macros work on the worksheet that is active when they are run -
whatever it is - even if it is the "wrong" one. There are many ways to
specify which worksheet to act on. Stay tuned.

Bob

"Diane Frost" <dfr...@pcblawfirm.com> wrote in message

news:#GmLFo2$CHA....@TK2MSFTNGP11.phx.gbl...

[Bob Kilmer]

If you use these macros on many workbooks, you might put this code into a
module in Personal.xls on each computer used to run them. If they are needed
in only one or a few workbooks, you might put the code into those workbooks.

I have named these procedures Roll, RollB, RollC, etc. because I am not very
creative. (Yeah, right. ;-)) You can rename them to whatever makes sense to
you, so long as they are unique in a workbook. It is best to be as explicit
as possible about which workbook and worksheet the macros work on, lest an
inadvertent action hose otherwise satisfactory work. I have provided several
examples of specifying worksheets. Pick a method that suits you and change
names as required.

Bob

'----------------------------------------------------------
Option Explicit

Private Sub Roll(rng As Range)
'Check the range size.
If rng.Cells.Count = 4 And _
(rng.Columns.Count = 1 Or rng.Rows.Count = 1) Then
With Application.WorksheetFunction
rng(rng.Count) = .Sum(Range(rng.Cells(1), rng.Cells(3)))
End With
rng.Cells(1) = rng(rng.Count)
Else
'Warn the user if range is wrong size.
MsgBox "Wrong range size (#" & CStr(rng.Cells.Count) & _
" !!). Range size must be 4 adjacent " & _
"cells in a row or a column.", , "U-Roll'em!"
End If
End Sub

Public Sub RollB()
Roll ThisWorkbook.Worksheets("Sheet1").Range("B5:B8")
'Works on the sheet named "Sheet1" in the workbook
'where this macro is stored - ThisWorkbook.
End Sub

Public Sub RollC()
Roll Workbooks("Book1.xls"). _
Worksheets("Sheet1").Range("C6:C9")
'Works on the specified range in
'Book1.xls, Sheet1.
'Must exist and be open.
End Sub

Public Sub RollD()
Roll ActiveSheet.Range("D7:D10")
'Works on the specified range in
'whatever workbook is active.
End Sub

Public Sub RollA()
Roll ActiveSheet.Cells
'Tests range checking of Roll().
End Sub

Public Sub RollButGoSlow()
'Throw up an "are you sure?" dialog.
Dim rng As Range
Set rng = ActiveSheet.Range("B8:E8")
Dim msg As String

msg = "Are you sure you want to do the range "
msg = msg & ActiveWorkbook.Name & ", "
msg = msg & ActiveSheet.Name
msg = msg & "(" & rng.Address & ")"

'Check answer and continue.
If (vbYes = _
MsgBox(msg, vbYesNo + vbDefaultButton2, _
"U-Roll'em!")) Then
Roll rng
'Works on the specified range in
'whatever worksheet is active.
End If
Set rng = Nothing
End Sub

"Diane Frost" <dfr...@pcblawfirm.com> wrote in message

news:#GmLFo2$CHA....@TK2MSFTNGP11.phx.gbl...