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]

do you mean like this?:

If Month(Date) = 1 Then
         If Sheets("Sheet7").Range("A1") = "" Then
         ActiveCell.Value = MainForm.TextBox1.Value
         Else
         Range("A1").End(xlDown).Offset(1, 0).Value = MainForm.TextBox1.Value
         ActiveSheet.Range("A1", ActiveSheet.Range("A1").End(xlDown)).Name = "MTDFOOD"
         End If
Else

 

[JackDanIce]

Untested, however, try:

Sub Macro1()

    Dim LR      As Long
    Dim mnth    As Long
    Dim arr     As Variant
    Const delim As String = "|"
    
    arr = Split("Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sept|Oct|Nov|Dec", delim)
    mnth = Month(Now())
    
    MsgBox "Current month is " & arr(mnth - 1), vbOKOnly
    
    With Sheets("Sheet7")
        LR = .Cells(.Rows.Count, mnth).End(xlUp).row
        If .Cells(1, mnth).Value = vbNullString Then
            .Cells(1, mnth).Value = MainForm.TextBox1.Value
        Else
            LR = LR + 1
            With .Cells(LR, mnth)
                .Value = MainForm.TextBox1.Value
                .Name = "MTDFOOD"
            End With
        End If
    End With
    
    Erase arr
    
End Sub

 

[dmt32]

Hi,
untested but see if this update to your code does what you want

   Dim c As Integer
    c = Month(Date)
    MsgBox "current month is " & MonthName(c, True)
    Sheets("Sheet7").Activate
    If Cells(1, c) = "" Then
        ActiveCell.Value = MainForm.TextBox1.Value
    Else
        Cells(1, c).End(xlDown).Select
        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = MainForm.TextBox1.Value
        ActiveCell.Range(Cells(1, c), Cells(Cells(Rows.Count, c).End(xlUp).Row, c)).Name = "MTDFOOD"

Dave

 

[Bill Rockenbach]

Yet more code Try this code to handle all months

Added month number to MTDFOOD to make it a unique name

Option Explicit
Private Sub CommandButton1_Click()
    Dim MonthNbr As Integer
    Dim NR As Long
    Dim ws As Worksheet
    '
    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) = TextBox1.Value
            .Range(.Cells(NR, 1), .Cells(NR, MonthNbr).End(xlUp)).Name = "MTDFOOD" & MonthNbr
        End If
    End With
End Sub

 

[chazrab]

Yet more code Try this code to handle all months

Added month number to MTDFOOD to make it a unique name

Option Explicit
Private Sub CommandButton1_Click()
    Dim MonthNbr As Integer
    Dim NR As Long
    Dim ws As Worksheet
    '
    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) = TextBox1.Value
            .Range(.Cells(NR, 1), .Cells(NR, MonthNbr).End(xlUp)).Name = "MTDFOOD" & MonthNbr
        End If
    End With
End Sub

Bill, I created Userform1, put Textbox1 on it, put the code in a button on it captioned "Enter Data", and created another button directly on the sheet which calls Userform1, entered 3 sample values(1, 2 and 3) in Texbox1, and it put each of these values in col A one under the other.

That said, I can't completely understand how this code works, so will this code know when to stop entering values in col A for month 1(Jan) and begin to enter values in col B for month 2(Feb) and so on ? In other words, if I enter a value at say 11:59 PM on 1/31/17 and
enter a new value at 12:01 AM on 2/1/17, will it put the new value in col B and so on through all the months of the year ?

Thanks for helping. I about to put this in a macro but wanted some clarification first.
cr
Kingwood, Tx

 

[JLGWhiz]

Bill, I created Userform1, put Textbox1 on it, put the code in a button on it captioned "Enter Data", and created another button directly on the sheet which calls Userform1, entered 3 sample values(1, 2 and 3) in Texbox1, and it put each of these values in col A one under the other.

That said, I can't completely understand how this code works, so will this code know when to stop entering values in col A for month 1(Jan) and begin to enter values in col B for month 2(Feb) and so on ? In other words, if I enter a value at say 11:59 PM on 1/31/17 and
enter a new value at 12:01 AM on 2/1/17, will it put the new value in col B and so on through all the months of the year ?

Thanks for helping. I about to put this in a macro but wanted some clarification first.
cr
Kingwood, Tx

Why not make a copy of your file and test the macro to see what it does?

 

[chazrab]

Why not make a copy of your file and test the macro to see what it does?

good point - doing that now. cr

 

[chazrab]

Why not make a copy of your file and test the macro to see what it does?

Just tested it and it works great - the beauty of Bill's code is that it does not require rewriting some of the same identical code lines 12 times for 12 months - although I don't 100& understand how his code works - and most importantly if the first entry in the month of Feb will be placed in cell B1, Mar C1...and so on...maybe you do. JLGWhiz, Bill - thanks for all your help. cr

 

[chazrab]

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