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

JDM_virgin

New Member
Joined
Jan 7, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
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
 

Attachments

  • december error.JPG
    december error.JPG
    96.1 KB · Views: 8
  • example of data captured from main worksheet.JPG
    example of data captured from main worksheet.JPG
    116.8 KB · Views: 8
  • january correct.JPG
    january correct.JPG
    146.4 KB · Views: 8
Last edited by a moderator:

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,135
You should share a sample workbook, containing the macro and enough test data

Bye
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,473
Office Version
  1. 2019
Platform
  1. Windows
Hi,

Welcome to forum.

See if following update to your code does what you want

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,126,961
Messages
5,621,847
Members
415,861
Latest member
Leetor72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top