• 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...

  • 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

helloakshay

New Member
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.
 

Attachments

  • sour.xlsm
    11.7 KB · Views: 7
  • dest.xlsm
    26.8 KB · Views: 6
See if this is what you want:

Code:
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
 
See if this is what you want:

Code:
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                        'what is the boolean variable doing here
        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            'what is the boolean variable doing here
                Exit For       ' While j is still in the loop why are inputting 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
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:
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

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:
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:
If Not Found Then
     .Cells(i, 6).Value = "Not Matched"
EndIf
Thanks/Ajesh
 
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:
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:
If Not Found Then
     .Cells(i, 6).Value = "Not Matched"
EndIf
Thanks/Ajesh
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.
 
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.
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
 
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
Very true. I totally get that as u say depends on what and how.
Thank you once again for help
 
Back
Top