Can we summarize the following code?

[LoveCandle]

Hi everyone,

Can we summarize the following code?

Sub MyTest()
For R = 5 To Sheet1.[C100].End(xlUp).Row

If Sheet1.Cells(R, 3) = Sheet2.[L4] Then

Sheet1.Cells(R, 22) = Sheet2.[J15]
Sheet1.Cells(R, 23) = Sheet2.[J16]
Sheet1.Cells(R, 24) = Sheet2.[J17]
Sheet1.Cells(R, 25) = Sheet2.[J18]
Sheet1.Cells(R, 26) = Sheet2.[J19]
Sheet1.Cells(R, 27) = Sheet2.[J20]
Sheet1.Cells(R, 28) = Sheet2.[J21]
Sheet1.Cells(R, 29) = Sheet2.[J22]
Sheet1.Cells(R, 30) = Sheet2.[J23]
Sheet1.Cells(R, 31) = Sheet2.[J24]
Sheet1.Cells(R, 32) = Sheet2.[J25]
Sheet1.Cells(R, 33) = Sheet2.[J26]
Sheet1.Cells(R, 34) = Sheet2.[J27]
Sheet1.Cells(R, 35) = Sheet2.[J28]
Sheet1.Cells(R, 36) = Sheet2.[J29]
Sheet1.Cells(R, 37) = Sheet2.[J30]
Sheet1.Cells(R, 38) = Sheet2.[J31]
Sheet1.Cells(R, 39) = Sheet2.[J32]
Sheet1.Cells(R, 40) = Sheet2.[J33]
Sheet1.Cells(R, 41) = Sheet2.[J34]
Sheet1.Cells(R, 42) = Sheet2.[J35]
Sheet1.Cells(R, 43) = Sheet2.[J36]
Sheet1.Cells(R, 44) = Sheet2.[J37]
Sheet1.Cells(R, 45) = Sheet2.[J38]
Sheet1.Cells(R, 46) = Sheet2.[K15]
Sheet1.Cells(R, 47) = Sheet2.[M15]
Sheet1.Cells(R, 48) = Sheet2.[G40]
Sheet1.Cells(R, 49) = Sheet2.[L40]
Sheet1.Cells(R, 50) = Sheet2.[L41]
Sheet1.Cells(R, 51) = Sheet2.[B32]
Sheet1.Cells(R, 52) = Sheet2.[B33]
Sheet1.Cells(R, 53) = Sheet2.[B34]
Sheet1.Cells(R, 54) = Sheet2.[B35]
Sheet1.Cells(R, 55) = Sheet2.[B36]
Sheet1.Cells(R, 56) = Sheet2.[B37]
Sheet1.Cells(R, 57) = Sheet2.[B38]

End If
Next

End Sub

[DonkeyOte]

Not entirely sure I follow the code unless you're saying there should be only one match of Sheet2 L4 on Sheet1 (in C range) ... else you're always overriding the results on Sheet2

Perhaps ?

Sub MyTest()
Dim vRow As Variant
Sheet1.Activate
vRow = Application.Match(Sheet2.Range("L4").Value, Sheet1.Range(Cells(5, "C"), Cells(100, "C").End(xlUp)), 0)
If IsNumeric(vRow) Then
    vRow = vRow + 4
    Sheet2.Range("J15:J38").Value = Application.Transpose(Sheet1.Range(Cells(vRow, 22), Cells(vRow, 45)).Value)
    Sheet2.Range("K15").Value = Sheet1.Cells(vRow, 46)
    Sheet2.Range("M15").Value = Sheet1.Cells(vRow, 47)
    Sheet2.Range("G40").Value = Sheet1.Cells(vRow, 48)
    Sheet2.Range("L40:L41").Value = Application.Transpose(Sheet1.Range(Cells(vRow, 49), Cells(vRow, 50)).Value)
    Sheet2.Range("B32:B38").Value = Application.Transpose(Sheet1.Range(Cells(vRow, 51), Cells(vRow, 57)).Value)
Else
    MsgBox "No Match Found in Column C on Sheet1"
End If
End Sub

[LoveCandle]

Thank you DonkeyOte for your fast reply,, your code is excellent, but it is doing the opposite of the code I supplied.

Can you please check it again??

[DonkeyOte]

Sorry, yes you are quite correct... I didn't read the code thoroughly enough, it would seem you are looping column C on Sheet1 and wherever the value matches the value of L4 on Sheet2 you're copying the various values from Sheet2 to your horizontal range on Sheet1... is that correct ?

[LoveCandle]

You are extermely right