VBA Find row and replace

[jamescooper]

Hello,

I have the following data in cells as below that I want to overwrite ID which is in column A of sheet 2 and the rest transposed into that row.

How would I do this please?

Set rng = Sheets("Entry - Accidents").Range("G5, D5, J7, D7, G7, D9, G9, D13, K13, D15, G15, D19, G19, J28, J26, J36, J32, J34, G17, J24, D17, AP3, D21, E26, E28, E36, E24, E38, E32, E34, E30, M20, S20, P20, S22, J9, J30, J38, G21, O22, G13, K5")

 

[JLGWhiz]

Here is one way.

Dim ary As Variant
 ary = Array("G5", "D5", "J7", "D7", "G7", "D9", "G9", "D13", "K13", "D15", "G15", "D19", "G19", "J28", "J26", _
 "J36", "J32", "J34", "G17", "J24", "D17", "AP3", "D21", "E26", "E28", "E36", "E24", "E38", "E32", "E34", "E30", _
 "M20", "S20", "P20", "S22", "J9", "J30", "J38", "G21", "O22", "G13", "K5")
With Sheets("Entry - Accidents")
    For i = LBound(ary) To UBound(ary)
        Sheets(2).Cells(2, i + 1) = .Range(ary(i)).Value 'Starts in column 1 and goes accross based on array count
    Next
End With

 

[jamescooper]

Here is one way.

Dim ary As Variant
 ary = Array("G5", "D5", "J7", "D7", "G7", "D9", "G9", "D13", "K13", "D15", "G15", "D19", "G19", "J28", "J26", _
 "J36", "J32", "J34", "G17", "J24", "D17", "AP3", "D21", "E26", "E28", "E36", "E24", "E38", "E32", "E34", "E30", _
 "M20", "S20", "P20", "S22", "J9", "J30", "J38", "G21", "O22", "G13", "K5")
With Sheets("Entry - Accidents")
    For i = LBound(ary) To UBound(ary)
        Sheets(2).Cells(2, i + 1) = .Range(ary(i)).Value 'Starts in column 1 and goes accross based on array count
    Next
End With

Hello thanks, it appears to overwrite the top row rather than the relevant row, so if in G5 is 3775, is should paste over the row when 3775 is in column A.

Sorry I may have not made myself clear.

 

[JLGWhiz]

so if in G5 is 3775, is should paste over the row when 3775 is in column A.

You did not specify that in the OP.

Dim ary As Variant, fn As Range
 ary = Array("G5", "D5", "J7", "D7", "G7", "D9", "G9", "D13", "K13", "D15", "G15", "D19", "G19", "J28", "J26", _
 "J36", "J32", "J34", "G17", "J24", "D17", "AP3", "D21", "E26", "E28", "E36", "E24", "E38", "E32", "E34", "E30", _
 "M20", "S20", "P20", "S22", "J9", "J30", "J38", "G21", "O22", "G13", "K5")
With Sheets("Entry - Accidents")
    Set fn = Sheets(2).Range("A:A").Find(.Range(ary(0)).Value, , xlValues, xlWhole)
        If Not fn Is Nothing Then
            For i = LBound(ary) To UBound(ary)
                Sheets(2).Cells(fn.Row, i + 1) = .Range(ary(i)).Value 'Starts in column 1 and goes accross based on array count
            Next
        End If
End With

 

[jamescooper]

You did not specify that in the OP.

Dim ary As Variant, fn As Range
 ary = Array("G5", "D5", "J7", "D7", "G7", "D9", "G9", "D13", "K13", "D15", "G15", "D19", "G19", "J28", "J26", _
 "J36", "J32", "J34", "G17", "J24", "D17", "AP3", "D21", "E26", "E28", "E36", "E24", "E38", "E32", "E34", "E30", _
 "M20", "S20", "P20", "S22", "J9", "J30", "J38", "G21", "O22", "G13", "K5")
With Sheets("Entry - Accidents")
    Set fn = Sheets(2).Range("A:A").Find(.Range(ary(0)).Value, , xlValues, xlWhole)
        If Not fn Is Nothing Then
            For i = LBound(ary) To UBound(ary)
                Sheets(2).Cells(fn.Row, i + 1) = .Range(ary(i)).Value 'Starts in column 1 and goes accross based on array count
            Next
        End If
End With

Thanks - works a treat!

 

[JLGWhiz]

Thanks - works a treat!

You're welcome,
regards, JLG