select active cells in loops excel vba 1

[slames]

I have a column of values that I need to check whether they are greater than zero or not. If they are greater than zero then I need to take the value from 3 columns to the right and place it on the column 3 places to the left. I then want it to move to the next row and do the same.

This is the code i am using:
Private Sub CommandButton2_Click()

Dim order As Range
Dim i As Integer

Set order = Range("h8:h74&quot
For i = 1 To order.Rows.Count


If order.Cells(i, h).Value > 0 Then


Range(i, h).Select
ActiveCell.Offset(0, -3) = ActiveCell.Offset(0, 4)
ActiveCell.Offset(0, -4) = ActiveCell.Offset(0, 4)
End If
i = i + 1


Next i

Range("h8:h74&quot.Select
Selection.ClearContents


End Sub


The problem I am having is that I can't get the next row down to be the active cell, so in the first row h8 is the active cell (but only if that was the last cell I clicked in), on the next loop I need it to be h9 so all the relative references work. How can I get the active cell to be the row the loop is on?

Hope this makes sense to someone,

Thanks

Steph

 

[xlbo]

You don't need to use activecell or select at all:
Range(i, h).Select
ActiveCell.Offset(0, -3) = ActiveCell.Offset(0, 4)
ActiveCell.Offset(0, -4) = ActiveCell.Offset(0, 4)

can be rewritten as:

Range(i, "h&quot.Offset(0, -3) = Range(i, "h&quot.Offset(0, 4)
Range(i, "h&quot.Offset(0, -4) = Range(i, "h&quot.Offset(0, 4)


Rgds, Geoff
It's Super Happy Campo Funtime!
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[slames]

When I replace my code with yours I get the following error:

Method.'Range' of object'_worksheet failed.

Do you have any ideas why this fails?

Thanks.

 

[xlbo]

which line does it fail on ??
You don't need the incrementing i either - it does that in the loop but I would use a for each construct on this one:

Dim order As Range

Set order = Range("h8:h74&quot
For each c in order
If c.Value > 0 Then
c.Offset(0, -3) = c.Offset(0, 4)
c.Offset(0, -4) = c.Offset(0, 4)
else
End If
Next


Rgds, Geoff
It's Super Happy Campo Funtime!
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[slames]

That works perfectly, thank you so much for your help.

Steph

 

[Bowers74]

I thought that xlbo deserved a star for helping slames out. Even though slames obviously didn't seem to think so.

XLBO here's your STAR!!!

If you can't be "The Best", be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!

 

[xlbo]

Thx Bowers

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244