• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Match with Previous Row - Do

Kaiser

Member
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.
 

Attachments

  • Match with Previous Row - Do.xls
    28 KB · Views: 6
Dear Kaiser,

Please try the below code..
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
 
Hi Kaiser,

Try putting this in worksheet:

Code:
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.
 

Attachments

  • Match with Previous Row - Do.xlsm
    16 KB · Views: 3
Hi Kaiser,

Try putting this in worksheet:

Code:
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.
 
Hi Kaiser,

Try putting this in worksheet:

Code:
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.
 
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.
 
Thanks vijay,

I was just experimenting.. :)

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

Code:
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.
 
Thanks vijay,

I was just experimenting.. :)

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

Code:
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
 
Back
Top