using variable in
--You dont need to mention the column number.
--Here the counter should not be zero..(modified)
Range("I1").Select
Selection.End(xlDown).Select
JobNumber = ActiveCell.Value
counter = 0
Do
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value < JobNumber Then
ActiveCell.Offset(-1, 0).Select
Exit Do
End If
counter = counter - 1
Loop
ActiveCell.Offset(0, -1).Select
If counter < 0 Then
Selection.Resize(counter).Select
End If
--
If this post helps click Yes
---------------
Jacob Skaria
"Don Acree" wrote:
The "counter" variable is intended to be a negative number. I want to use
that to tell the program how far to resize a range. If "counter" = -3, then:
Selection.Resize(counter, 0).Select
command would make range expand to 3 rows higher.
Your new instruction bypasses this line, because "counter" is not greater
than 1.
Thx
"Jacob Skaria" wrote:
Don; try the below....(untested)
Range("I1").Select
Selection.End(xlDown).Select
JobNumber = ActiveCell.Value
counter = 0
Do
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value < JobNumber Then
ActiveCell.Offset(-1, 0).Select
Exit Do
End If
counter = counter - 1
Loop
ActiveCell.Offset(0, -1).Select
If counter 1 Then
Selection.Resize(counter).Select
End If
If this post helps click Yes
---------------
Jacob Skaria
"Don Acree" wrote:
I get a Runtime error '1004' when it encounters the last line here. I guess
the program cannot understand the use of the variable "counter" in the last
line.
Range("I1").Select
Selection.End(xlDown).Select
JobNumber = ActiveCell.Value
counter = 0
Do
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value < JobNumber Then
ActiveCell.Offset(-1, 0).Select
Exit Do
End If
counter = counter - 1
Loop
ActiveCell.Offset(0, -1).Select
Selection.Resize(counter, 0).Select
|