Transactions are entered into col A of Sheet7 every day from the following macro code. When the current month changes to the next month, a new set of items are posted in col B and so on. Here is the simple code:
MainForm is just the userform from which a value is entered in Textbox1 and added to the end of col A
in this case, January, since Month(Now()) =1. As you can see, MTDFOOD is just a dynamic range name that changes, actually increases with each item added for that month.
I could write a sub for the common code that repeats itself in all If ...Then statements above and also use
Select Case, but the idea is to simplify this to an even greater degree if possible. I have 12 blocks of pretty much the same code for each of the 12 months of the year.
The columns change(A1, B1, C1, etc) as each respective month changes, so a Gosub doesn't seem like it would be very useful. All i want to do is simplify this process if it can be done any further.
Thanks for anyone's help on this.
cr
Code:
If Month(Now()) = 1 Then
Sheets("Sheet7").Activate
If Range("A1") = "" Then
ActiveCell.Value = MainForm.TextBox1.Value
Else
Range("A1").End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = MainForm.TextBox1.Value
ActiveSheet.Range("A1", Range("A1").End(xlDown)).Name = "MTDFOOD"
End If
Else
If Month(Now()) = 2 Then
MsgBox "current month is Feb"
Sheets("Sheet7").Activate
If Range("B1") = "" Then
ActiveCell.Value = MainForm.TextBox1.Value
Else
Range("B1").End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = MainForm.TextBox1.Value
ActiveSheet.Range("B1", Range("B1").End(xlDown)).Name = "MTDFOOD"
End If
Else
...
in this case, January, since Month(Now()) =1. As you can see, MTDFOOD is just a dynamic range name that changes, actually increases with each item added for that month.
I could write a sub for the common code that repeats itself in all If ...Then statements above and also use
Select Case, but the idea is to simplify this to an even greater degree if possible. I have 12 blocks of pretty much the same code for each of the 12 months of the year.
The columns change(A1, B1, C1, etc) as each respective month changes, so a Gosub doesn't seem like it would be very useful. All i want to do is simplify this process if it can be done any further.
Thanks for anyone's help on this.
cr