Morning,
I have written some code, which does work but I don't think it's the right code for what I am trying to achieve. I broke the run of the code after it had been running for 15 minutes
There's 2 parts to this
On sheet 'freight detail' there are a selection of different drop down options (determined by data validation) The user, for example has a drop down in cell C4, this is a list of months (Jan to Dec)
(D4 is a formula pulling the month number)
On sheet 'Freight data' is a huge data set of about 700,000 rows. On this sheet in column T, is a booking date. If the month selected in C4 equals the month in T5 (for example) then I want AO5 to equal 1, if it doesn't then it should be blank
If the user deletes the month in cell C4 then I need to assign 1 to every cell in AO (this is because 1 resembles a match on the month, if there is no month selected in C4 then every call is a match
The problem is the process of checking off every single cell is taking way too long......is there a better way ?
I have written some code, which does work but I don't think it's the right code for what I am trying to achieve. I broke the run of the code after it had been running for 15 minutes
There's 2 parts to this
On sheet 'freight detail' there are a selection of different drop down options (determined by data validation) The user, for example has a drop down in cell C4, this is a list of months (Jan to Dec)
(D4 is a formula pulling the month number)
On sheet 'Freight data' is a huge data set of about 700,000 rows. On this sheet in column T, is a booking date. If the month selected in C4 equals the month in T5 (for example) then I want AO5 to equal 1, if it doesn't then it should be blank
If the user deletes the month in cell C4 then I need to assign 1 to every cell in AO (this is because 1 resembles a match on the month, if there is no month selected in C4 then every call is a match
The problem is the process of checking off every single cell is taking way too long......is there a better way ?
Code:
Sub bkgmonth()
Application.ScreenUpdating = False
Dim monthnum As Integer
Dim monthrec As Integer
Dim r As Range
On Error Resume Next
monthnum = Sheets("Freight Detail").Range("D4")
endrow = Cells(Rows.Count, 1).End(xlUp).Row
Set r = Range(Cells(2, 41), Cells(endrow, 41))
If Sheets("Freight Detail").Range("c4") = "" Then
For Each cell In r
If cell.Value = 0 Then
cell.Value = cell.Value + 1
End If
Next
End If
Sheets("Freight Data").Activate
Range(Cells(2, 41), Cells(endrow, 41)).ClearContents
For d = 2 To endrow
monthrec = Month(Cells(d, 20))
If monthrec = monthnum Then
Cells(d, 41) = 1
End If
Next d
Sheets("Freight Detail").Activate
Application.ScreenUpdating = True
End Sub