1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup with reptitive values and diffrent rows

Discussion in 'VBA Macros' started by helloakshay, Dec 23, 2018.

  1. helloakshay

    helloakshay New Member

    Messages:
    24
    Hi All

    I am working on two files the master file and source file

    The source file has information about volumes but only 100 rows with 3 column
    Staff id queuename volumes
    16001005 process A 99
    16001005 process B 101
    16001006 Process A 200

    The master file has information about
    Staff id Queuename, Volumes

    *Queue name in master file has all the queue names from A-Z while in the source file we have only those queue names which operator worked in that month. So in the source file we have might 100 rows this month next month we might have 75/ 125.

    So the objective here is to vlookup if staff id in master is equal to staff id in the source file and then if Q name is equal for that staff id update volumes in destination file.

    Issue is the rows in source file will keep changing based work done by operators in that month and Q name will be appearing for all staff ids who worked in that month.
    While the structure in master file remains the same for all staff ids and all q name whether they process or not.

    Ultimately i want to update volumes in destination file


    I am unable to run a vlookup through vba. so wanted u r suggestion i have attached the files.

    Attached Files:

  2. Ajesh

    Ajesh Active Member

    Messages:
    180
    See if this is what you want:

    Code (vb):

    Private Sub CommandButton1_Click()
    Dim sWB As Workbook
    Dim sWsht As Worksheet, dWsht As Worksheet
    Dim SLrow As Long, DLrow As Long
    Dim i, j As Long
    Dim Found As Boolean

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With

    On Error GoTo CleanExit

    Set sWB = Workbooks.Open(ThisWorkbook.Path & "\Sour.xlsm")
    Set sWsht = sWB.Worksheets("Sheet1")
    Set dWsht = ThisWorkbook.Worksheets("Sheet1")

    SLrow = sWsht.Range("B" & Rows.Count).End(xlUp).Row
    DLrow = dWsht.Range("A" & Rows.Count).End(xlUp).Row

    With dWsht
        For i = 2 To DLrow
            Found = False
            For j = 2 To SLrow
                If .Cells(i, 3).Value = sWsht.Cells(j, 1).Value And .Cells(i, 5).Value = sWsht.Cells(j, 2).Value Then
                    .Cells(i, 6).Value = sWsht.Cells(j, 3).Value
                    Found = True
                    Exit For
                End If
            Next j
            If Not Found Then
                .Cells(i, 6).Value = "Not Matched"
            End If
        Next i
    End With

    sWB.Close False

    CleanExit:

    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With

    End Sub
     
    Thanks/Ajesh
    Thomas Kuriakose likes this.
  3. helloakshay

    helloakshay New Member

    Messages:
    24
    Thanks ajesh for your time and reply. i am just working with the solution you have provided will just get back to you.
  4. helloakshay

    helloakshay New Member

    Messages:
    24
    Thanks ajaesh. it worked
  5. helloakshay

    helloakshay New Member

    Messages:
    24
    Ajesh could you tel me regarding the Boolean variable and Exit for in this context what they are actually checking and how does it fit in the code. i marked my questions also in the code as comments.
    Many Thanx
    Last edited: Jan 8, 2019
  6. Ajesh

    Ajesh Active Member

    Messages:
    180
    For every i loop we initialize Found as False and then change it to True when we get a match. Found variable will be changed to True only if we have found a match for below line:
    Code (vb):

    If .Cells(i, 3).Value = sWsht.Cells(j, 1).Value And .Cells(i, 5).Value = sWsht.Cells(j, 2).Value Then
     
    Once we have found a match, we no longer need to continue the j loop and exit it so as to carry on with next i.

    If we do not get a match, Found will be remain false and below If condition will kick in:
    Code (vb):

    If Not Found Then
         .Cells(i, 6).Value = "Not Matched"
    EndIf
     
    Thanks/Ajesh
    helloakshay likes this.
  7. helloakshay

    helloakshay New Member

    Messages:
    24
    Thank you ajesh . one last question. is it a best practice to use a boolean variable in every for loop/ loops so that we are telling the code what do when found the value and when not found the value.
  8. Ajesh

    Ajesh Active Member

    Messages:
    180
    It depends on what you are trying to achieve and how your loops are constructed. There's no "one" best method to do a task in excel. You can do same thing in multiple ways and all/most of them may be considered as one of the best. :) So it all depends on "what" and "how".

    Thanks/Ajesh
    helloakshay likes this.
  9. helloakshay

    helloakshay New Member

    Messages:
    24
    Very true. I totally get that as u say depends on what and how.
    Thank you once again for help

Share This Page