0

I have a formula inserted info a cell using VBA, but it paste the same formula in all rows from the range.

Is there any way to increment the row number so it can match the row number it is in?

This is my code:

    lastrow = Sheets("Sheet1").Cells(rows.Count, "I").End(xlUp).Row
    myCopyRow = 2
    For myRow = 1 To lastrow
        If Sheets("Sheet1").Cells(myRow, "I") = "x" Then
            Sheets("Sheet2").Cells(myCopyRow, "A") = "=CONCATENATE(""        - "",B2,"" - "",C2,"" - "",D2)"
            Sheets("Sheet2").Cells(myCopyRow, "B") = Sheets("Sheet1").Cells(myRow, "F")
            Sheets("Sheet2").Cells(myCopyRow, "C") = Sheets("Sheet1").Cells(myRow, "B")
            Sheets("Sheet2").Cells(myCopyRow, "D") = Sheets("Sheet1").Cells(myRow, "D")
            myCopyRow = myCopyRow + 1
        End If
    Next myRow

I need that CONCATENATE formula to increase to B3,C3, D3 and so on when the macro paste it on the proper row.

If I could use "myCopyRow" to do that would be awesome.

Rafoid
  • 1
  • 1
  • Just concatenate `myCopyRow` in using `&`. Alternately `Replace(""=CONCATENATE("" - "",B2,"" - "",C2,"" - "",D2)",2,myCopyRow)` – BigBen Feb 16 '22 at 19:42

0 Answers0