Macros to compute Sales figures Differences between Months

[slc]

I am trying to create a macro to compute the sales figures difference
between month. A1 to A12 cell contain the sales figure for Jan to Dec.
B1 cell contains the difference between the last 2 months.

So in the month of Oct, I want B1 to contain +A9-A8 i.e. the diff
between Sep and Aug after running the macro.

In the next month Nov, I want B1 to contain +A10-A9 and so on.

Anyone have any idea how I can create this macro to. Thanks for your
help in advance.

 

[Bob Phillips]

monthNum = Month(Date)
Range("B1").Formula = "=A" & monthNum - 1 & "-A" & monthNum - 2

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[slc]

Thanks Bob.

One further questions. If I want to use row number instead how should
I do it. I have the following macro statement and the 3rd statement is
incorrect.

Range("A1").Select
Selection.End(xlDown).Select
RowNum = Row()
Range("B2").Formula = "=B" & RowNum & "-B" & RowNum - 1

Basically, the same thing i.e. I first go to cell A1, then goto the
last cell. get the row number of the last cell and then put the diff
b/w last cell and 2nd last cell in B1 cell.


Thanks once again.

 

[losmac]

Here is your code:

Sub ComputeSales()
Dim myMonth As Long

myMonth = Month(Date)

ThisWorkbook.Worksheets(2).Range("B1") = _
ThisWorkbook.Worksheets(2).Range("A" & myMonth - 2) - _
ThisWorkbook.Worksheets(2).Range("A" & myMonth - 1)

End Sub

losmac

 

[Bob Phillips]

rowNum = Range("A1").End(xlDown).Row
Range("B2").Formula = "=B" & rowNum & "-B" & rowNum - 1

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)