VBA Lmonth code not recognising the months of december (12)

[JDM_virgin]

Hi Everyone,
I have an iusse with a VBA code that i have written for my company's vast excel sheet we use to track job information. below is a very quick summary of what the sheet does to help explain why i have the code as i do. I am in no way an expert and the codes i have written came from a lot of googling and reading, they work but are probably not the most efficient way of writing them so sorry in advance!

Ok so the spreadsheet allows the user to load job data so we can trac quantities made, late orders, costs, hours worked etc. each job gets a unique code that we use for a reference (and yes a database would have been better but i have zero knoweldge of access and others). At the end of each month we, as a department have to report out the total cost of jobs worked and the hours worked on those jobs- here is where my macro comes in. On a report sheet, the user can select the month to search from a drop down list (1-12), once a month is selected and the macro run, it searches the main data sheet for any job that was started in that month, if it finds a job in that month, it copies the unique job number and pastes it into the report sheet. Once it has run the report sheet is populated with all the job numbers for a given month. I then use a series of VLOOKUP formula to gather information based on those job numbers. this collection of data is then sent out. All this works fine apart from when we search for December (12). The macros doesnt copy any job numbers and instead populates all the job numbers with a 0.

I have cut and shut the macro so all it does is look for month 12 and it still messes up, however if it is left it will complete the cycle and my message box will eventually pop up to say it is complete. If i run any other month it works flawlessly. Below is the code and i will attach images of what is happening. Any help would be hugely appreciated, could it also be a glitch in Excel?

Code for Macro:

Sub Search_Month()


Dim datasheet As Worksheet
Set datasheet = Sheet2
Dim Mreport As Worksheet
Set Mreport = Sheet9

Dim Lmonth As Integer
search = Range("p4").Value

Dim i As Integer

Mreport.Unprotect Password:=rapid1

Mreport.Range("a3:L300").ClearContents
Range("a3:L300").ClearFormats

datasheet.Activate

For i = 7 To ActiveSheet.UsedRange.Rows.Count

Lmonth = Month(Cells(i, 6))

    If Lmonth = search Then
   
Mreport.Activate
Range("a2:L2").Copy
Range("a300").End(xlUp).Offset(1, 0).PasteSpecial

datasheet.Activate
Range(Cells(i, 2), Cells(i + 3, 2)).Copy
Mreport.Activate
Range("b300").End(xlUp).PasteSpecial xlPasteValues
datasheet.Activate

End If

Next i

Mreport.Activate

Dim Rng As Range 'calculates total cost for month
    Dim c As Range
    Set Rng = Range("G3:G" & Range("G1").End(xlDown).Row)
    Set c = Range("G3").End(xlDown).Offset(1, 0)
    c.Formula = "=SUM(" & Rng.Address(False, False) & ")"
   
Dim hours As Range 'calculates total hours worked for month
Dim h As Range
    Set hours = Range("L3:L" & Range("L1").End(xlDown).Row)
    Set h = Range("L3").End(xlDown).Offset(1, 0)
    h.Formula = "=SUM(" & hours.Address(False, False) & ")"
   
   
If search = "1" Then
Range("Q7") = datasheet.Range("N5000").End(xlUp) / Mreport.Range("A3", Range("A" & Rows.Count).End(xlUp)).Rows.Count

ElseIf search = "2" Then
Range("Q7") = datasheet.Range("AS5000").End(xlUp) / Mreport.Range("A3", Range("A" & Rows.Count).End(xlUp)).Rows.Count

ElseIf search = "3" Then
Range("Q7") = datasheet.Range("BU5000").End(xlUp) / Mreport.Range("A3", Range("A" & Rows.Count).End(xlUp)).Rows.Count

ElseIf search = "4" Then
Range("Q7") = datasheet.Range("CZ5000").End(xlUp) / Mreport.Range("A3", Range("A" & Rows.Count).End(xlUp)).Rows.Count

ElseIf search = "5" Then
Range("Q7") = datasheet.Range("ED5000").End(xlUp) / Mreport.Range("A3", Range("A" & Rows.Count).End(xlUp)).Rows.Count

ElseIf search = "6" Then
Range("Q7") = datasheet.Range("FI5000").End(xlUp) / Mreport.Range("A3", Range("A" & Rows.Count).End(xlUp)).Rows.Count

ElseIf search = "7" Then
Range("Q7") = datasheet.Range("GM5000").End(xlUp) / Mreport.Range("A3", Range("A" & Rows.Count).End(xlUp)).Rows.Count

ElseIf search = "8" Then
Range("Q7") = datasheet.Range("HR5000").End(xlUp) / Mreport.Range("A3", Range("A" & Rows.Count).End(xlUp)).Rows.Count

ElseIf search = "9" Then
Range("Q7") = datasheet.Range("IW5000").End(xlUp) / Mreport.Range("A3", Range("A" & Rows.Count).End(xlUp)).Rows.Count

ElseIf search = "10" Then
Range("Q7") = datasheet.Range("KA5000").End(xlUp) / Mreport.Range("A3", Range("A" & Rows.Count).End(xlUp)).Rows.Count

ElseIf search = "11" Then
Range("Q7") = datasheet.Range("LF5000").End(xlUp) / Mreport.Range("A3", Range("A" & Rows.Count).End(xlUp)).Rows.Count

ElseIf search = "12" Then
Range("Q7") = datasheet.Range("MJ5000").End(xlUp) / Mreport.Range("A3", Range("A" & Rows.Count).End(xlUp)).Rows.Count


End If

'Mreport.Protect Password:=rapid1

MsgBox "End of Month Report Updated"

End Sub

 

[Anthony47]

You should share a sample workbook, containing the macro and enough test data

Bye

 

[dmt32]

Hi,

Welcome to forum.

See if following update to your code does what you want

Option Explicit
Sub Search_Month()
    Dim datasheet       As Worksheet, Mreport As Worksheet
    Dim Lmonth          As Integer, MonthIndex As Integer, i As Integer
    'calculates total cost for month
    Dim rng             As Range, c As Range
    'calculates total hours worked for month
    Dim hours           As Range, h As Range
    Dim LookupAddress   As String
 
    Set datasheet = Sheet2
 
    Set Mreport = Sheet9
 
    With Mreport
    MonthIndex = Val(.Range("p4").Value)
    If MonthIndex < 1 Or MonthIndex > 12 Then Exit Sub
 
        .Unprotect Password:="rapid1"
        .Range("a3:L300").ClearContents
        .Range("a3:L300").ClearFormats

 
    LookupAddress = Choose(MonthIndex, "N", "AS", "BU", "CZ", "ED", "FI", "GM", "HR", "IW", "KA", "LF", "MJ")
 
        For i = 7 To datasheet.UsedRange.Rows.Count
         
            Lmonth = Month(datasheet.Cells(i, 6).Value)
         
            If Lmonth = MonthIndex Then
             
                .Range("a2:L2").Copy
                .Range("a300").End(xlUp).Offset(1, 0).PasteSpecial
                Application.CutCopyMode = False
             
                datasheet.Range(datasheet.Cells(i, 2), datasheet.Cells(i + 3, 2)).Copy
                .Range("b300").End(xlUp).PasteSpecial xlPasteValues
                Application.CutCopyMode = False
             
             
            End If
         
        Next i
     
        Set rng = .Range("G3:G" & .Range("G1").End(xlDown).Row)
        Set c = .Range("G3").End(xlDown).Offset(1, 0)
        c.Formula = "=SUM(" & rng.Address(False, False) & ")"
     
        Set hours = .Range("L3:L" & .Range("L1").End(xlDown).Row)
        Set h = .Range("L3").End(xlDown).Offset(1, 0)
        h.Formula = "=SUM(" & hours.Address(False, False) & ")"
     
        .Range("Q7") = datasheet.Range(LookupAddress & "5000").End(xlUp) / .Range("A3", .Range("A" & .Rows.Count).End(xlUp)).Rows.Count
    End With
 
    'Mreport.Protect Password:="rapid1"
 
    MsgBox "End of Month Report Updated", 64, "Updated"
 
End Sub

You had declared and assigned object variables to your worksheets which would be used to qualify the ranges – You rarely need to use select or activate in code.

Please note that I have only glanced through your code & my changes are untested & may need some adjustment / correction.

If you are still having issues then as already suggested, place copy of your workbook with some dummy data on a file sharing site like dropbox or use MrExcel Addin XL2BB - Excel Range to BBCode there are plenty here to offer assistance.

Hope Helpful

Dave