JDM_virgin
New Member
- Joined
- Jan 7, 2021
- Messages
- 1
- Office Version
- 365
- Platform
- 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:
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
Last edited by a moderator: