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

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

It is always a good idea to tell us what non-working code is supposed to be
doing. My gut feeling is this can be done with less code, but I am not
totally sure what you are ultimately trying to accomplish.

--
Rick (MVP - Excel)


"Don Acree" <Don wrote in message
...
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

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

I am trying to count the number of jobs with the same job number in column
"I", then I go one column over the get the values associated with it - in
this case number of hours worked at each job. The "counter" variable is used
to count the number of occurences, but I am using a negative number for
"counter" so that when I move down in column I, the cursor moves over one
column to left and then extends the range up from there in order to copy.


"Rick Rothstein" wrote:

It is always a good idea to tell us what non-working code is supposed to be
doing. My gut feeling is this can be done with less code, but I am not
totally sure what you are ultimately trying to accomplish.

--
Rick (MVP - Excel)


"Don Acree" <Don wrote in message
...
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

I think Resize doesn't accept negative number. So, I think your code below

ActiveCell.Offset(0, -1).Select
Selection.Resize(counter, 0).Select

changing to

ActiveCell.Offset(counter, -1).Select
Selection.Resize(-counter, 0).Select

might do what you want.

Keiji

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

Sory for wrong code.

ActiveCell.Offset(counter, -1).Select
Selection.Resize(-counter, 0).Select

should be

If counter < 0 then
ActiveCell.Offset(counter, -1).Select
Selection.Resize(-counter, 0).Select
End If

by the way, I may be wrong but I think your code seems to give counter 0
in any case.

Keiji

keiji kounoike wrote:
I think Resize doesn't accept negative number. So, I think your code below

ActiveCell.Offset(0, -1).Select
Selection.Resize(counter, 0).Select

changing to

ActiveCell.Offset(counter, -1).Select
Selection.Resize(-counter, 0).Select

might do what you want.

Keiji

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

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