Working with Date variables

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

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.

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