Anyone know of a shorter, better way to do this...

[chazrab]

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:

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
...

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

 

[JLGWhiz]

Bill, this code works great - one question - where will this code place the first value entered in Textbox1 of Mainform for Month = 2(Feb)? I can't see where this code causes the new month values to begin in cells B1, C1, D1, etc["ry this code to handle all months"] for each respective month change ?

Thanks for your help.
cr

NR = .Cells(1, MonthNbr)(Rows.Count, 1).End(xlUp).Row + 1

The variable NR finds the next available row on the destination sheet, wherever that row might be. The code uses that variable to create a range equal to the copied source range which it pastes only the values into, eliminating the need for the PasteSpecial method. The MonthNbr variable determines the column number to be pasted to.

 

[chazrab]

NR = .Cells(1, MonthNbr)(Rows.Count, 1).End(xlUp).Row + 1

The variable NR finds the next available row on the destination sheet, wherever that row might be. The code uses that variable to create a range equal to the copied source range which it pastes only the values into, eliminating the need for the PasteSpecial method. The MonthNbr variable determines the column number to be pasted to.

Thanks -now I get it. What causes the values of Textbox1 to change columns is the variable MonthNbr in .Cells(1,MonthNbr), which is the column number, and since Month(Now()) is a number, it "matches" Now() =1 being Jan and places the values in col 1. When the exact second after midnight that Feb rolls around, Month(Now()) would then = 2 and MonthNbr would = 2, matching it and placing it in col B =2, and so on and so on.

I really appreciate your help. This very simple code block opens up a whole new world for me in an effort to make the code in my app simpler, faster and more accurate as opposed to repeating 12 code blocks of nearly identical code for each month, just for the sake of changing months to put values in different columns.



cr

 

[Bill Rockenbach]

The code will post to the current month column. Thus when the month changes to February the code will post to the February column until the current month changes to March etc.

The “Cells” property can refer to a single cell range by using row and column parameters. Cells(Row,Col) refers to a single cell range by row and col. Eg. Cells(1,2) refers to Range B1. Variables can be used to set the row and column parameters.

The variable "MonthNbr" is used for the Col parameter and is assigned the current month. ie Jan=1 Feb=2 etc this variable is used in the code to identify which column data is to be posted.. So when the month changes the code posts data to the new month column.

Wherever you see "Cells(1,MonthNbr)" in the code the number 1 refers to row 1 of the current month column.
Wherever you see "Cells(NR,MonthNbr)" in the code the NR refers to the next row available in the current month column.
The current month column is controlled by the variable MonthNbr.

 

[chazrab]

Bill - thanks for the very excellent explanation. I totally understand what this code does now. What if I want to SUM MTDFOOD & MonthNbr in cell say, A1 of Sheets("BUDGET") every month since, the Named range changes each month by the value of MonthNbr ? I tried this but it didn't work:

   Dim MonthNbr As Integer
    Dim NR As Long
    Dim ws As Worksheet
    Dim r as Range

MonthNbr = Month(Now())
    Set ws = Worksheets("Sheet7")
    With ws
        .Activate
        If .Cells(1, MonthNbr) = "" Then
            .Cells(1, MonthNbr) = TextBox1.Value
        Else
            NR = .Cells(1, MonthNbr)(Rows.Count, 1).End(xlUp).Row + 1
            .Cells(NR, MonthNbr) = MainForm.TextBox1.Value
        Set r =     .Range(.Cells(NR, 1), .Cells(NR, MonthNbr).End(xlUp)).Name = "MTDFOOD" & MonthNbr
       Range("X1").value = SUM(r)  X1 is any cell that would sum "MTDFOOD" & MonthNbr each time.
  
 End If

Thanks for all your help.
oops - left out something

Worksheets("BUDGET").TextBox2.Value = Sheets("Sheet7").Range("SUMMTDFOOD").Value
TextBox2.Text = Format(TextBox2.Text, "$ #,##0.00")

Thx, cr




cr
End With

 

[Bill Rockenbach]

Not sure what you want. If your looking to update your budget with actual data then this will accumulate totals in the Budget worksheet row 1, by month in columns A through L for month to date(MTD) and column M for YTD.

    Dim MonthNbr As Integer
    Dim NR As Long
    Dim ws As Worksheet
    Dim Budget As Worksheet
    Dim r As Range
    MonthNbr = Month(Now())
    Set ws = Worksheets("Sheet7")
    Set Budget = Sheets("Budget")
    With ws
        .Select
        If .Cells(1, MonthNbr) = "" Then
            .Cells(1, MonthNbr) = TextBox1.Value
            '
            'Update The Budget
            Budget.Range("M1") = Budget.Range("M1") + TextBox1.Value
            Budget.Cells(1, MonthNbr) = Budget.Cells(1, MonthNbr) + TextBox1.Value
        Else
            NR = .Cells(1, MonthNbr)(Rows.Count, 1).End(xlUp).Row + 1
            .Cells(NR, MonthNbr) = MainForm.TextBox1.Value
            .Range(.Cells(NR, 1), .Cells(NR, MonthNbr).End(xlUp)).Name = "MTDFOOD" & MonthNbr
            '
            'Update The Budget
            Budget.Range("M1") = Budget.Range("M1") + TextBox1.Value
            Budget.Cells(1, MonthNbr) = Budget.Cells(1, MonthNbr) + TextBox1.Value
        End If
    End With
End Sub

 

[chazrab]

the only thing left I need to do is just sum this range name "MTDFOOD" & MonthNbr for each month and put the result in cell M1 of Sheet 7. The value in M1 will always be overwritten when months change.
I added these lines to your code

Range("M1").Select
Range("M1").value = "=SUM(MTDFOOD & MonthNbr)"

but I get #NAME in cell M1 when these code lines run

Thx

cr

 

[Bill Rockenbach]

If you use my previous post ode you would already have the months total.

No need to select cells to write to them
No need to use the "Value" Property as it is the default property of Range.

Here is the correct code when using Excel functions in VBA

Range("M1") = Application.WorksheetFunction.Sum(Range(MTDFOOD & MonthNbr))

 

[chazrab]

Bill - finally got it!- everything works great ! -yours was the very simplest of all the code solutions proposed. I really appreciate all your help.

cr
Kingwood, Tx

 

[Bill Rockenbach]

Chaz,
Glad I could help a fellow Texan