CODEWITHSUNDEEP
excelvbavlookup
rohail nisar
rohail nisar

Reputation: 89

fill up values using vlookup vba code in multiple rows and colums

enter image description here enter image description here

i am trying to populate 4 coulms and 130 rows from b2:b130 in display sheet using vlookup from bills sheet using rowid i created.

my data as in attached image.if anyone can help me with this that will be great.

Upvotes: 1

Views: 1325

Answers (3)

FaneDuru
FaneDuru

Reputation: 42236

Try the next code, please. It should be very fast and does not increase the workbook size like in case of formulas:

Sub copyRangeForSpecRows()
 Dim firstRow As Long, lastRow As Long, shS As Worksheet, shD As Worksheet, El As Variant
 Dim arrEX As Variant, arrGY As Variant, arrIZ As Variant, arrKAA As Variant
 Dim pasteRow As Long, lastCopyRow As Long, arrRows As Variant, i As Long, k As Long
 
 Set shS = Sheets("Bills") 'use here your sheet to copy from
 Set shD = Sheets("Display")
 
 firstRow = 5: lastRow = 130
 pasteRow = CLng(shD.Range("T" & firstRow).Value)
 lastCopyRow = CLng(shD.Range("T" & firstRow + lastRow).Value)
 
 ReDim arrEX(1 To lastRow, 1 To 1): ReDim arrGY(1 To lastRow, 1 To 1)
 ReDim arrIZ(1 To lastRow, 1 To 1): ReDim arrKAA(1 To lastRow, 1 To 1)
 arrRows = shD.Range(shD.cells(firstRow, "T"), shD.cells(lastRow + firstRow - 1, "T")).Value
 
 For i = pasteRow To lastCopyRow
    For Each El In arrRows
        If i = CLng(El) Then
            k = k + 1
            arrEX(k, 1) = shS.Range("X" & i).Value
            arrGY(k, 1) = shS.Range("Y" & i).Value
            arrIZ(k, 1) = shS.Range("Z" & i).Value
            arrKAA(k, 1) = shS.Range("AA" & i).Value
        End If
    Next
 Next i
 
 shD.Range("E5:E" & 4 + lastRow).Value = arrEX
 shD.Range("G5:G" & 4 + lastRow).Value = arrGY
 shD.Range("I5:I" & 4 + lastRow).Value = arrIZ
 shD.Range("K5:K" & 4 + lastRow).Value = arrKAA
 MsgBox "Ready..."
End Sub

Upvotes: 2

Mech
Mech

Reputation: 4015

Based on how small scale this is and where you said you are just a starter with vba, I kept it simple(ish) while getting the desired result.

Commented for understanding.

Option Explicit

Sub populateData()
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim wsDisp As Worksheet: Set wsDisp = wb.Worksheets("Display")
    Dim wsBill As Worksheet: Set wsBill = wb.Worksheets("Bills")
    Dim LastRow As Long
    Dim i As Long
    
    LastRow = wsDisp.Cells(wsDisp.Rows.Count, "B").End(xlUp).Row                                                            ' finds the last row in column B of the "Display" worksheet
    
    On Error Resume Next                                                                                                    ' bypasses errors such as unmatched values
    For i = 1 To LastRow                                                                                                    ' loop until the last row containing data
        wsDisp.Cells(i, 5).Value = wsBill.Cells(Application.Match(wsDisp.Cells(i, 20).Value, wsBill.Range("W:W"), 0), 24)   ' populates "GIDC Gas Paid" row in "Display" worksheet
        wsDisp.Cells(i, 7).Value = wsBill.Cells(Application.Match(wsDisp.Cells(i, 20).Value, wsBill.Range("W:W"), 0), 25)   ' populates "GST-GIDC Gas Paid" row in "Display" worksheet
        wsDisp.Cells(i, 9).Value = wsBill.Cells(Application.Match(wsDisp.Cells(i, 20).Value, wsBill.Range("W:W"), 0), 26)   ' populates "GIDC Booking Paid" row in "Display" worksheet
        wsDisp.Cells(i, 11).Value = wsBill.Cells(Application.Match(wsDisp.Cells(i, 20).Value, wsBill.Range("W:W"), 0), 27)  ' populates "GST-GIDC Booking Paid" row in "Display" worksheet
    Next i                                                                                                                  ' iterates to the next number in the loop
End Sub

Upvotes: 1

MD Ismail Hosen
MD Ismail Hosen

Reputation: 118

You should use index and Match function instead of Vlookup and if you want hard code then i will suggest you that go from row 1 to lastrow and check if both Sr.No and RowId match then take that data into an array and at the last paste the array data in Display Sheet.

Upvotes: -1

Related Questions