CODEWITHSUNDEEP
excelvba
GetSome _
GetSome _

Reputation: 59

VBA how to improve this code and make it more efficient?

I have this code for my vba excel. i am repeating the same code again and again to add my values from sheet2 to sheet1. any help?

    For j = 1 To Sheet1LastRow
    For i = 1 To Sheet2LastRow
        If Worksheets("sheet1").Cells(j, 7).Value = Worksheets("sheet2 ").Cells(i, 1).Value Then
            Worksheets("sheet1").Cells(j , 11).Value = Worksheets("sheet2").Cells(i ,1 ).Value
            Worksheets("sheet1").Cells(j, 12).Value = Worksheets("sheet2").Cells(i, 2).Value
            Worksheets("sheet1").Cells(j, 13).Value = Worksheets("sheet2").Cells(i, 3).Value
            Worksheets("sheet1").Cells(j, 14).Value = Worksheets("sheet2").Cells(i, 4).Value


        Else
        End If
Next i
Next j

Upvotes: 1

Views: 59

Answers (1)

Tim Williams
Tim Williams

Reputation: 166136

Contiguous ranges can be copied in one operation:

Dim sht1 As Worksheet, sht2 As Worksheet  

Set sht1  = Worksheets("Sheet1")
Set sht2  = Worksheets("Sheet2")

For j = 1 To Sheet1LastRow
    For i = 1 To Sheet2LastRow
        If sht1.Cells(j, 7).Value = sht2.Cells(i, 1).Value Then
                sht1.Cells(j, 11).Resize(1, 4).Value = _
                              sht2.Cells(i, 1).Resize(1, 4).Value
        End If
    Next i
Next j

Upvotes: 1

Related Questions