Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home

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.

for some reason when i do my logic check like

Do Until DataRange.Cells(i, j) = myDate

I've also tried

DataRange.Cells(i, j).value = mydate,
DateValue(DataRange.Cells(i, j)) = DateValue(mydate)
datarange.cells(i, j) = datevalue(mydate)

but none of this seems to work. I can't read the Japanese help manual, so I am having a hard time looking up the functions.

thanks in advance

YL


Check out our Excel VBA Resources

Re: Working with Date variables

Posted by Celia on August 03, 0100 12:31 AM
Dim arrsize As Integer Dim j As Integer Dim Arr() As Single arrsize = 4 Do Until DateRange.Cells(arrsize, 1) = InputDate arrsize = arrsize + 1 Loop Arr(j - 4) = DataRange.Cells(j, i) Next j


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

Function ReadData(ByRef DataRange As Range)
Application.Volatile
Dim cell As Range
For Each cell In DataRange
If cell = Range("InputRange") Then
ReadData= DataRange(1, 1).Address(False, False) & ":" & cell.Address(False, False)
Exit For
End If
Next
End Function

Or perhaps the function is supposed to return the number of cells from the first cell in the DataRange to the first cell that matches the date in the "InputRange" ? In which case, try :-

Function ReadData(ByRef DataRange As Range)
Application.Volatile
Dim cell As Range
For Each cell In DataRange
If cell = Range("InputRange") Then
ReadData = Range(DataRange(1, 1), cell).Count
Exit For
End If
Next
End Function

Please post again if the above is no good.

Celia


Thanks, I got it to work now

Posted by Yu-Kuan on August 06, 0100 5:27 PM
Thanks a million, I got it to work now.

yu-kuan


Re: Working with Date variables

Posted by Celia on August 02, 0100 8:15 PM

YL
Can't really know what's wrong without seeing all of your code.
However, see if this stucture does what you want :-

Sub MatchMyDate()
Dim MyDate As Range, DataRange As Range, Cell As Range
Set MyDate = Range("A1")
Set DataRange = Range("B1:B20")
For Each Cell In DataRange
If Cell = MyDate Then
MsgBox "A matching date is in cell " & Cell.Address(False, False)
End If
Next
End Sub

Celia



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?

Code as follows:

InputDate = Worksheets(1).Range("UserInput")

Function ReadData(ByRef DataRange As Range, InputDate As Date, ByRef DateRange As Range, _
ByRef DataArr As Variant, ByRef i As Integer)

Dim arrsize As Integer
Dim j As Integer
Dim Arr() As Single

arrsize = 4

Do Until DateRange.Cells(arrsize, 1) = InputDate
arrsize = arrsize + 1
Loop

ReDim Arr(arrsize - 4)

For j = 4 To arrsize 'change here to change dates
Arr(j - 4) = DataRange.Cells(j, i)
Next j

ReadData = Arr

End Function


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?

Code as follows:

InputDate = Worksheets(1).Range("UserInput")

Function ReadData(ByRef DataRange As Range, InputDate As Date, ByRef DateRange As Range, _
ByRef DataArr As Variant, ByRef i As Integer)

Dim arrsize As Integer
Dim j As Integer
Dim Arr() As Single

arrsize = 4

Do Until DateRange.Cells(arrsize, 1) = InputDate
arrsize = arrsize + 1
Loop

ReDim Arr(arrsize - 4)

For j = 4 To arrsize 'change here to change dates
Arr(j - 4) = DataRange.Cells(j, i)
Next j

ReadData = Arr

End Function


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


YL
This returns the range as a string :-

Function FindXRange() As String
Dim scrollR As Range
Dim cell As Range
Dim ansR As Range
Set scrollR = Range("$A$2:$A$65536")
For Each cell In scrollR
If IsEmpty(cell) Then
Set ansR = Range(scrollR(1, 1), cell.Offset(-1, 0))
Exit For
End If
Next
FindXRange = ansR.Address(False, False)
End Function

It can be written also as :-

Function FndXRng() As String
FndXRng = Range(Range("A2"), Range("A2").End(xlDown)).Address(False, False)
End Function


Hope this helps.

Celia


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.