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.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

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" wrote in message
...
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.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

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.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

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

U¿ytkownik "slc" napisa³ w wiadomo¶ci
...
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.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

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)

"slc" wrote in message
...
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.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/