vlookup with line insert

sachin483
2StarLounger
Posts: 101
Joined: 03 Feb 2018, 04:20

vlookup with line insert

Post by sachin483 »

i have to do vlookup based on position and code
ie:- for each and every position and code vlookup with multiple lines will get repeated with city and number
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: vlookup with line insert

Post by HansV »

Here is a macro:

Code: Select all

Sub Multiply()
    Dim r1 As Long
    Dim r2 As Long
    Dim r3 As Long
    Dim m1 As Long
    Dim m2 As Long
    Dim po As String
    Dim co As String
    Dim ci As String
    Dim nu As Long
    Application.ScreenUpdating = False
    Range("J3:M" & Rows.Count).ClearContents
    m1 = Range("A2").End(xlDown).Row
    m2 = Range("E2").End(xlDown).Row
    r3 = 2
    For r1 = 3 To m1
        po = Range("A" & r1).Value
        co = Range("B" & r1).Value
        For r2 = 3 To m2
            If Range("E" & r2).Value = co Then
                ci = Range("F" & r2).Value
                nu = Range("G" & r2).Value
                r3 = r3 + 1
                Range("J" & r3).Value = po
                Range("K" & r3).Value = co
                Range("L" & r3).Value = ci
                Range("M" & r3).Value = nu
            End If
        Next r2
    Next r1
    Range("J2:M" & r3).HorizontalAlignment = xlHAlignCenter
    Application.ScreenUpdating = True
End Sub
Best wishes,
Hans

sachin483
2StarLounger
Posts: 101
Joined: 03 Feb 2018, 04:20

vlookup with line insert

Post by sachin483 »

Thanks a lot working fine