Previous Topic Next Topic

Help with row command

Solved/Closed
Mark - Sep 26, 2008 at 01:55 PM
 DAOQ - Sep 26, 2008 at 02:57 PM
Hello,

I'd like excel to move the number one to move in column A to row user the user selects with the mouse (or the active cell is) for instance. If user selects C6 the macro would put the numer one in A6 and when user selects another cell in a another row for instance D9 it would "delete" the number one in A6 and move it to A9 and so on...

So far I managed this, but it doesn't get me very long.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = Range("c6").Address Then _
Target.Offset(0, -2).Range("a1") = 1

End Sub

Can someone please help me,

Regards,
Mark
Related:

2 responses

Answer 1 / 2
DAOQ
Sep 26, 2008 at 02:19 PM
Copy the sub below into the worksheet you want it to be in.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Make a variable to hold the range the user selected.
Dim RR As Range

'If the selection is not in the first column...
If Target.Column <> 1 Then

'If this row does not have a '1' in the cell...
If Cells(Target.Row, 1) <> "1" Then

'Hold the range the user selected.
Set RR = Target

'Find the '1' that was in the first column.
Range("A1").Select
Selection.End(xlDown).Select

'Clear the '1'
ActiveCell.FormulaR1C1 = ""

'Put the '1' in the correct row.
Cells(RR.Row, 1) = "1"

'Put the selection back where the user had it.
RR.Select

End If

End If

End Sub
0
Mark
Sep 26, 2008 at 02:33 PM
Hi DAOQ,

Thank you for your immidiate reply! ;) You just made my day.

Best regards,
Mark
0
Answer 2 / 2
DAOQ
Sep 26, 2008 at 02:57 PM
You're Welcome, when testing it I just saw that it doesn't work in the very first row it leaves the 1 in row 1. In order to fix this you need a slight mod... (below...)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Make a variable to hold the range the user selected.
Dim RR As Range

'If the selection is not in the first column...
If Target.Column <> 1 Then

'If this row does not have a '1' in the cell...
If Cells(Target.Row, 1) <> "1" Then

'Hold the range the user selected.
Set RR = Target

'Find the '1' that was in the first column.
Range("A1").Select
ActiveCell.FormulaR1C1 = "" '(This extra line will clear the '1' if it's in the first row.)
Selection.End(xlDown).Select

'Clear the '1'
ActiveCell.FormulaR1C1 = ""

'Put the '1' in the correct row.
Cells(RR.Row, 1) = "1"

'Put the selection back where the user had it.
RR.Select

End If

End If

End Sub
0