values based on the datetime stamp given by the user
Hi,
I have a column H with the values as datetime stamp format (dd/mm/yyyy HH:MM AM/PM).
I need to delete the values after the date which i specify for ex, 31/01/2011 05:00 PM. The values after this date and time should be considered as "Yes" in column I and the value should be "No" for all before that date and time.
The criteria differs, this time it was 31/01/2011 05:00 PM and differs day by day. Hence the criteria should be specified by the user. Per input the value in column I should change.
As of now, we manually check for the dates and deselect the values. The file contains 18,000 rows and we have to manually check for the dates in column H.
Also, the date format should always be in the format dd/mm/yyyy irrespective of the excel in which we copy data from.
[VBA]Sub test_YNColumnHDates()
Dim d As Date
d = DateSerial(2012, 3, 20) + 0.5 'Noon
YNColumnHDates d
End Sub
Sub YNColumnHDates(aDate As Date)
Dim cell As Range, hRange As Range
On Error GoTo EndSub
'http://vbaexpress.com/kb/getarticle.php?kb_id=1035
SpeedOn
Set hRange = Range("H2", Range("H" & Rows.Count).End(xlUp))
hRange.NumberFormat = "dd/mm/yyyy"
For Each cell In hRange
With cell
If .Value > aDate Then
Range("I" & .Row).Value = "Yes"
Else
Range("I" & .Row).Value = "No"
End If
End With
Next cell
There are several ways for the user to set the date to pass to the routine. I would not recommend an InputBox() method. You will have to worry about the user entering the correct datetime format. There is a DateTime control that you can add in a Userform.
For this 2nd example, I used the more simple method of storing the date to pass to the routine in cell A1 of the current sheet. Add an ActiveX CommandButton from the Developer's tab from Insert.
I placed a comment in the code to tell you where to get SpeedOn and SpeedOf. Comment or delete those routines as you like but I recommend that you at least use parts of those routines since you have so much data to process.
Click the Design Mode button in the Developer tab after inserting the Command button so that a doubleclick opens that button's Sub and add the line as I did.
[VBA]
Private Sub CommandButton1_Click()
YNColumnHDates Range("A1").Value
End Sub[/VBA]
I tried as you said and showing me the error in the speedoff routine.
I used only sppedon and speedoff. The error message was "runtime error 1004" method calculation of object application failed. I have attached the sample sheet for your reference.
You missed the global variable when you copied the speed routines. It must be before other routines at the top of the module. I also recommend Option Explicit as the first line in your Modules.
[VBA]Option Explicit
Public glb_origCalculationMode As Integer
[/VBA]
I use it so often that I put it in a Module in my Personal.xls or Personal.xlsb file. That way, I can just drag and drop it into a separate workbook if needed. Of course you can break parts out but I am lazy so I just made it into a standard routine that I could use.