Back to Excel VBA archive index

Back to archive home

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

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 a million, I got it to work now.

yu-kuan

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

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

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

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.

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

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

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.

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.