PDA

View Full Version : values based on the datetime stamp given by the user



sindhuja
03-17-2012, 09:42 AM
Hi,:friends:

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.:doh:


Also, the date format should always be in the format dd/mm/yyyy irrespective of the excel in which we copy data from.

Can some one please assist with this.:help

-Sindhuja

Kenneth Hobs
03-17-2012, 04:26 PM
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

EndSub:
SpeedOff
End Sub

sindhuja
03-18-2012, 01:26 AM
Thank you for the coding..

i want the user to fix the criteria. In the input box the datetime should be defined and as per the criteria, yes , no should be there in column i.

Speedon - compile error sub or function not defined.

the above was the error when i tried running the macro..

kindly assist

Kenneth Hobs
03-18-2012, 06:56 AM
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.


Private Sub CommandButton1_Click()
YNColumnHDates Range("A1").Value
End Sub

sindhuja
03-18-2012, 10:11 PM
Thanks for the explanation Kenneth..

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.

please help me with this error.

-Sindhuja

Kenneth Hobs
03-19-2012, 05:38 AM
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.

Option Explicit
Public glb_origCalculationMode As Integer

sindhuja
03-19-2012, 08:54 PM
Its perfect now.. thank you so much Kenneth. .

What is the use of speedon and speedoff, can we use this any vba coding..

Kenneth Hobs
03-20-2012, 05:53 AM
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.