Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Date in Cell VS a Variable Date to Hide Row
I am trying to compare a date in a cell format "mm/dd/yyyy" against a date
which had been input into a userform [same format] to ultimately hide a row. When I use the following code all rows are hidden. "StartDate" is the variable from the UserForm [Public StartDate As Date] Range("D2").Select ' Set Do loop to stop when an empty cell is reached. Do Until IsEmpty(ActiveCell) ' Insert your code here. If ActiveCell.Value StartDate Then Selection.EntireRow.Hidden = True Else ActiveCell.Offset(1, 0).Select Loop If I convert the "D" column to the "Number" format and compare against the number format date for "StartDate" the macro works. Where am I formating incorrectly? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Date in Cell VS a Variable Date to Hide Row
You can try it this way:
Range("D2").Select ' Set Do loop to stop when an empty cell is reached. Do Until IsEmpty(ActiveCell) ' Insert your code here. If ActiveCell.Value2 clng(StartDate) Then ActiveCell.EntireRow.Hidden = True ActiveCell.Offset(1, 0).Select Loop -- Regards, Tom Ogilvy "JimI" wrote in message ... I am trying to compare a date in a cell format "mm/dd/yyyy" against a date which had been input into a userform [same format] to ultimately hide a row. When I use the following code all rows are hidden. "StartDate" is the variable from the UserForm [Public StartDate As Date] Range("D2").Select ' Set Do loop to stop when an empty cell is reached. Do Until IsEmpty(ActiveCell) ' Insert your code here. If ActiveCell.Value StartDate Then Selection.EntireRow.Hidden = True Else ActiveCell.Offset(1, 0).Select Loop If I convert the "D" column to the "Number" format and compare against the number format date for "StartDate" the macro works. Where am I formating incorrectly? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Date in Cell VS a Variable Date to Hide Row
Thank you Tom,
I think that I have realized that I am not populating the StartDate variable with the date from the userform. I have tried to define "Public StartDate as Date" and am picking it up from the Date1 variable box in the Userform. When I use MsgBox for StartDate and Date1 after I exit Userform I get no data so I am not filling the variable. I have used UserForm in the past so I am going to have to work on this one. Thanks for your help. "Tom Ogilvy" wrote: You can try it this way: Range("D2").Select ' Set Do loop to stop when an empty cell is reached. Do Until IsEmpty(ActiveCell) ' Insert your code here. If ActiveCell.Value2 clng(StartDate) Then ActiveCell.EntireRow.Hidden = True ActiveCell.Offset(1, 0).Select Loop -- Regards, Tom Ogilvy "JimI" wrote in message ... I am trying to compare a date in a cell format "mm/dd/yyyy" against a date which had been input into a userform [same format] to ultimately hide a row. When I use the following code all rows are hidden. "StartDate" is the variable from the UserForm [Public StartDate As Date] Range("D2").Select ' Set Do loop to stop when an empty cell is reached. Do Until IsEmpty(ActiveCell) ' Insert your code here. If ActiveCell.Value StartDate Then Selection.EntireRow.Hidden = True Else ActiveCell.Offset(1, 0).Select Loop If I convert the "D" column to the "Number" format and compare against the number format date for "StartDate" the macro works. Where am I formating incorrectly? Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Date in Cell VS a Variable Date to Hide Row
when you unload the userform, the textbox is cleared. You need to set the
StartDate in the code before you unload the userform. -- Regards, Tom Ogilvy "JimI" wrote in message ... Thank you Tom, I think that I have realized that I am not populating the StartDate variable with the date from the userform. I have tried to define "Public StartDate as Date" and am picking it up from the Date1 variable box in the Userform. When I use MsgBox for StartDate and Date1 after I exit Userform I get no data so I am not filling the variable. I have used UserForm in the past so I am going to have to work on this one. Thanks for your help. "Tom Ogilvy" wrote: You can try it this way: Range("D2").Select ' Set Do loop to stop when an empty cell is reached. Do Until IsEmpty(ActiveCell) ' Insert your code here. If ActiveCell.Value2 clng(StartDate) Then ActiveCell.EntireRow.Hidden = True ActiveCell.Offset(1, 0).Select Loop -- Regards, Tom Ogilvy "JimI" wrote in message ... I am trying to compare a date in a cell format "mm/dd/yyyy" against a date which had been input into a userform [same format] to ultimately hide a row. When I use the following code all rows are hidden. "StartDate" is the variable from the UserForm [Public StartDate As Date] Range("D2").Select ' Set Do loop to stop when an empty cell is reached. Do Until IsEmpty(ActiveCell) ' Insert your code here. If ActiveCell.Value StartDate Then Selection.EntireRow.Hidden = True Else ActiveCell.Offset(1, 0).Select Loop If I convert the "D" column to the "Number" format and compare against the number format date for "StartDate" the macro works. Where am I formating incorrectly? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How capture a date variable within a text cell? | Excel Discussion (Misc queries) | |||
How to compare a cell containing a date in sumif criteria | Excel Worksheet Functions | |||
Compare dates (one cell not in date format) | Excel Discussion (Misc queries) | |||
How do I add a date formula to a cell but hide the contents with . | Excel Discussion (Misc queries) | |||
calc constant date from variable date & return with ability to rn. | Excel Worksheet Functions |