Macro Problem ActiveCell

[ajbojey]

I need HELP!
Here is the macro that I am trying to expand to 200 rows, each row has to work independent of each other with the same macro.
What I want to do is to be able to select any cell in 1st columb from which to run this macro for that row as the rows have to be done one at a time. How do I redefine the Range command to accept just the active cell for the row that I am in?
Here is the first row which works fine.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/18/2002 by Alex Bojey
'
' Keyboard Shortcut: Ctrl+z
'
Range("E2:M2").Select
Selection.Copy
Range("D2").Select
ActiveSheet.Paste
Range("M2").Select
Selection.ClearContents
End Sub

 

[Dragracer]

Try this

<pre>
Sub Macro1()
Dim i As Integer

i = ActiveCell.Row
Range("E" & i & ":" & "M" & i).Copy
Range("D2").Select
ActiveSheet.Paste
Range("M2").ClearContents
End Sub

</pre>

 

[ajbojey]

Thanks Dragracer,
This only works the 1st row.
Alex

 

[Dragracer]

Hi Alex

Sorry I guess I misunderstood what You were after. I thought you wanted to only the active row.

Looking at your original code, all you are doing is moving a range left by one cell. If this is all you need to do why not just select column D rows 1 thru 200 and delete, shifting cells left.

If you do need a loop post back with more details.

 

[stevebausch]

I can only comprehend your description "select any cell in the first column".

Okay, I did that.

What do you want to happen next???

Give us some pseudo-code on the user-interaction ("Select any cell"), and the desired behavior ("Start the macro, the macro....") and I will work with you to create the code.

 

[ajbojey]

Hi Dragracer,
I only want to do one row at a time , the row that is selected in columb "A"
Alex

 

[ajbojey]

Hi stevebausch,
I have to do one row at a time, so I was going to use columb "A" to highlite the row to perform the calculations.
I could make a macro for each row and it works fine, however, then I would have 200 macros to work with. I am trying to make one macro to do the row that is selected.
Alex

 

[Dragracer]

Alex, you want to have your macro move the data from columns E thru M one cloumn left of your active row? Is this correct. If it is give this modified version a try.

<pre>
Sub Movestuff()
Dim i As Integer

i = ActiveCell.Row
Range("E" & i & ":" & "M" & i).Cut Destination:=Range("D" & i)

End Sub

</pre>

 

[ajbojey]

Hi Dragracer,
We are just about there. It does what I want except for one thing, by using "cut" it clears the M cell for new entry but the new entry is not accepted in furthere calculations. We have to use "copy" instead of "cut" and then clear M cell at the end.
Alex

 

[Dragracer]

Sorry Alex I was just trying to save a step. Just change cut to copy and add the clear contents line.

<pre>
Sub Movestuff()
Dim i As Integer

i = ActiveCell.Row
Range("E" & i & ":" & "M" & i).Copy Destination:=Range("D" & i)
Range("M" & i).ClearContents
End Sub

</pre>