using variable in

[Don Acree]

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

 

[Jacob Skaria]

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

 

[Rick Rothstein]

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.

 

[Don Acree]

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

 

[Don Acree]

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.

 

[keiji kounoike]

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

 

[keiji kounoike]

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

 

[Jacob Skaria]

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