Macro Problem ActiveCell

ajbojey

New Member
Joined
Oct 15, 2002
Messages
14
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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Dragracer

Board Regular
Joined
Jun 9, 2002
Messages
151
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>
 

Dragracer

Board Regular
Joined
Jun 9, 2002
Messages
151
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

Well-known Member
Joined
May 11, 2002
Messages
810

ADVERTISEMENT

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

New Member
Joined
Oct 15, 2002
Messages
14
Hi Dragracer,
I only want to do one row at a time , the row that is selected in columb "A"
Alex
 

ajbojey

New Member
Joined
Oct 15, 2002
Messages
14

ADVERTISEMENT

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

Board Regular
Joined
Jun 9, 2002
Messages
151
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

New Member
Joined
Oct 15, 2002
Messages
14
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

Board Regular
Joined
Jun 9, 2002
Messages
151
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>
 

Forum statistics

Threads
1,144,770
Messages
5,726,187
Members
422,661
Latest member
foxleinlady

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top