PDA

View Full Version : Better to user ReDim ?



bassman71
10-03-2006, 09:17 AM
Hello, the following seems like I could use ReDim statement. I am looping/counting through a vertical range and stopping when the value in the cell to the left = a particular time, then using that count I set the range. (This is part of a bigger loop) The challenge is the Count will vary with each iteration. Is there any way using redim to assess the number in range?

Dim TestOffset As Long, TimeT As Long ' I've reformatted time as integer

Do Until ActiveCell(1, 0).Value > (TimeT + TestOffset) - 1
ActiveCell.Offset(1, 0).Select
Count = Count + 1
Loop
ActiveCell.Offset(-Count, 0).Select
Set rng1 = Range(ActiveCell, ActiveCell.Offset(Count))

Many thanks.......RO

Zack Barresse
10-03-2006, 09:27 AM
Hi there,

ReDim is for array's, which you are not using here. You could use an array here. You could also not use the Select statement. I can't see all of your code, but maybe you could post a real world example of what you are trying to do along with the full code. Maybe we could get you some working code.

mdmackillop
10-03-2006, 11:20 AM
Something like
Sub SetRange()
Dim TestOffset As Long, TimeT As Long ' I've reformatted time as integer
Dim rng1 As Range, cel as Range
For Each cel In Range(ActiveCell, ActiveCell.End(xlDown))
If cel > (TimeT + TestOffset) - 1 Then Exit For
Next
Set rng1 = Range(ActiveCell, cel)
rng1.Select
End Sub
or to check the column to the left for values (if that's what you mean)

For Each cel In Range(ActiveCell, ActiveCell.End(xlDown))
If cel.Offset(, -1) > (TimeT + TestOffset) - 1 Then Exit For
Next