Archive of Mr Excel Message Board

Working with Date variables

Posted by Yu-Kuan on August 02, 2000 6:33 PM

Can someone help with the comparison of Date variables? i'm doing this in vba, not from sheet formulas. I have the user entering a date in a cell, which is then read into a Date variable (say, myDate) then I am trying to scroll through a range of cells all containing dates to compare with myDate to see if i can find the same date as myDate.

Why is it necessary to declare a date variable?What is the result that you require from the function?I'm not too clear what the function is supposed to do, but if it is to return the range of cells from the first cell in the DataRange to the first cell that matches the date in the "InputRange", then what about something like this :-

Re: Working with Date variables

Posted by Yu-Kuan on August 02, 0100 9:26 PM

Hi Celia,

Thanks for your help. Yeah, I've already tried comparing Range variables with Range variables, and I know that it works. However, since I declared myDate as a Date variable, I would like to know how to compare the date values within the cells w/ the Date variable. It seems that if I was just going to compare ranges, then i don't need to go through the trouble of declaring a Date variable?

Re: Working with Date variables

Posted by YU-Kuan on August 03, 0100 9:33 PM

Hi Celia,

Thanks for being so patient. I've been trying your For Each cell method, cycling through each of the cells in the range as a collection. However, I've been having problems making it work. This is a different function than the previous one that I posted, but it still does something very similar - this function picks a column and set it as a range. It then attempts to cycle through every cell in this column until it finds the next empty one, and then returns a range that starts from cells(2, 1) to the last nonempty cell.

Function FindXRange() As Range

Dim scrollR As Range Dim cell As Range Dim ansR As Range

Set scrollR = ActiveSheet.Columns(1) For Each cell In scrollR If IsEmpty(cell) Then Set ansR = Range(ActiveSheet.Cells(2, 1), cell.Offset(-1, 0)) Exit For End If Next

Set FindXRange = ansR

End Function

Somehow the For Each...Next command is not cycling through each of the cells in Column 1 of the worksheet. When I stepped through the debugger, the For Each loop only executed once, and the value of cell stayed as "Empty". Any ideas?

again, thanks a million for being so patient =)

YL

Re: Working with Date variables

Posted by david on August 02, 0100 9:48 PM

Hopefully celia is helping you well.I have a severe headache from my problem and having trouble understanding what you are trying to do but one helpful thing is making sure that you are choosing the correct range using the select funtion and also in the macro using the value function and comparing the dates that way. It is cofusing because of excels date codes, but it is easier for excel to compare numbers thatn it is dates. Hope some of this rambling helps you a little.

DAvid

Re: Working with Date variables

Posted by Yu-Kuan on August 02, 0100 10:40 PM

Hi Celia,

Thanks for your help. Yeah, I've already tried comparing Range variables with Range variables, and I know that it works. However, since I declared myDate as a Date variable, I would like to know how to compare the date values within the cells w/ the Date variable. It seems that if I was just going to compare ranges, then i don't need to go through the trouble of declaring a Date variable?

Re: Working with Date variables

Posted by Celia on August 03, 0100 11:07 PM

Dim cell As Range Dim ansR As Range Set scrollR = ActiveSheet.Columns(1) For Each cell In scrollR If IsEmpty(cell) Then Set ansR = Range(ActiveSheet.Cells(2, 1), cell.Offset(-1, 0)) Exit For End If Next