CODEWITHSUNDEEP
vbaif-statementcopy
MY Choi
MY Choi

Reputation: 1

VBA in Excel using If, Copy&Paste

I am a very beginner in VBA. I would like to automatically copy and paste if a certain condition is met. If Row G is "N" then copy from Range(Cells(Row+1,"h"), Cells(Row,"bg")) and paste to Range(Cells(Row+1,"h"), Cells(Row,"bg")) and add "Data substituted" in the substituted Row "bh". There are codes I've been made so far, '13' runtime error occurs whenever I implemented the code below. I am very lost please help me! Thank you in advance.

Sub Sbustitute()

    Dim nRows As Long, LastRow As Long, Row As Long, Column As Range, Color As Long
    nRows = 2
    LastRow = Cells(Rows.Count, "b").End(xlUp).Row
      For Row = LastRow To nRows Step -1
        Set Cell = Cells(Row, "g")
        If Cell = "N" Then
            Range(Cells(Row + 1, "h"), Cells(Row + 1, "bg")).Copy Range(Cells(Row, "h"), Cells(Row, "bg"))
            ActiveSheet.Paste
            Cells(Row, "bh") = "Data substituted" 
        End If
    Next
End Sub

Upvotes: 0

Views: 66

Answers (1)

BZngr
BZngr

Reputation: 681

Maybe the following is what you are after. Rather than Range.Copy, this version assigns the Value between the two Ranges.

    Sub Substitute()
        Dim nRows As Long, LastRow As Long, currentRow As Long, Column As Range, Color As Long

        nRows = 2
        LastRow = Cells(Rows.Count, "b").End(xlUp).Row
        For currentRow = LastRow To nRows Step -1
            Set Cell = Cells(currentRow , "g")
            If Cell = "N" Then
                Range(Cells(currentRow + 1, "h"), Cells(currentRow + 1, "bg")).Value = Range(Cells(currentRow , "h"), Cells(currentRow , "bg")).Value
                Cells(currentRow, "bh") = "Data substituted"
            End If
        Next
    End Sub

Upvotes: 2

Related Questions