Match with Previous Row - Do

[Kaiser]

Regards, can any brother may help me with a formula to do a small exercise as provided in the excel sheet. The requirement is also given in the excel sheet. Thanking you.

 

[vijay.vizzu]

Dear Kaiser,

Please try the below code..

Sub CopyAboveRow()
    Dim sRng As Range
    Dim iRow As Integer
    Dim iCol As Integer
   
    Set sRng = Range("A2:F5")
    Range("A2").Select
    For iRow = 2 To 5
        For iCol = 1 To 5
            If Cells(iRow, iCol).Value = "Do" Then
                Cells(iRow, iCol).Select
                ActiveCell.Value = ActiveCell.Offset(-1, 0).Value
            End If
        Next iCol
    Next iRow
End Sub

 

[Faseeh]

Hi Kaiser,

Try putting this in worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = "Do" Then Target.Value = Target.Offset(-1, 0)
End Sub

See attached file also.

 

[Kaiser]

Hi Kaiser,

Try putting this in worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = "Do" Then Target.Value = Target.Offset(-1, 0)
End Sub

See attached file also.

Dear Faseeh, thanks bro. But, after I put the code how shall I run it. please advise.

 

[Kaiser]

Hi Kaiser,

Try putting this in worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = "Do" Then Target.Value = Target.Offset(-1, 0)
End Sub

See attached file also.

Brother is it possible to give a Button to run the Macro, please advise.

 

[Kaiser]

Brother is it possible to give a Button to run the Macro, please advise.

 

[vijay.vizzu]

Dear Kaiser..

You can't put this into any button click event. becoz, it is triggered with Worksheet_Change event. So whenever the selection will change, first it will check, if the target cell value is "Do" (case sensitive),if true, then it will copy the above cell's value to current cell.

 

[Faseeh]

Thanks vijay,

I was just experimenting..

Here is a little improved one, previous was giving error when i tried to delete multiple cells selected.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo HandleError
If Target.Value = "Do" Then Target.Value = Target.Offset(-1, 0)
Exit Sub
HandleError:
Err.Clear
End Sub

@Kaiser

Please download and replace the code.

 

[Kaiser]

Thanks vijay,

I was just experimenting..

Here is a little improved one, previous was giving error when i tried to delete multiple cells selected.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo HandleError
If Target.Value = "Do" Then Target.Value = Target.Offset(-1, 0)
Exit Sub
HandleError:
Err.Clear
End Sub

@Kaiser

Please download and replace the code.

Thank you to both my brother for the concern.
Kaiser