2 sources, 2 Vlookup in 1 sheet

[Kooper]

Hi everyone,

Im searching the net for about 2hrs and i cant find the macro code for what im looking for. I have attached the excel file on how to code. Hope you can help me with my problem.

Thank you.


Book1.xlsx

[dilipandey]

Hi Kooper,

You can integrate below code for 1st vlookup in your main code:-

ActiveCell.Formula = "=VLOOKUP(A4,Data1!A:B,2,0)"

and for second vlookup :-

ActiveCell.Formula = "=VLOOKUP(C4,Data2!A:B,2,0)"

Regards,
DILIPandey

<click on below 'star' if this helps>

[Kooper]

Hi dilipandey,

Im sorry i forgot to mention the Vlookup with Looping.


Thanks.

[dilipandey]

Okay.. no worries, you can use below code:-

Sub aa()

Range("B4").Select
Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1)).Formula = "=VLOOKUP(A:A,Data1!A:B,2,0)"

Range("D4").Select
Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1)).Formula = "=VLOOKUP(C:C,Data2!A:B,2,0)"

End Sub

Regards,
DILIPandey

<click on below 'star' if this helps>

[Kooper]

Hi dilipandy,

How about this?

Sub aa()

Range("B4").Select <--- loop
Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1)).Formula = "=VLOOKUP(A:A,Data1!A:B,2,0)"

Range("D4").Select <--- loop
Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1)).Formula = "=VLOOKUP(C:C,Data2!A:B,2,0)"

End Sub

[dilipandey]

Hi Kooper,

Code will automatically take care of looping and hence not required

Regards,
DILIPandey

<click on below 'star' if this helps>

[Kooper]

Hi dilipandy,

I have here an example code of vlookup loop. however, i cant manage to work this on the 2nd lookup. Hope you can help me on this.

I also attached my excel file. The cell highlighted in yellow is my problem.

with this code below, i want the 2nd lookup be integrated in the code im using while using the 2nd data named "Data2"

Private Sub WorkSheet_SelectionChange(ByVal Target As Range)

Dim oDat As Range, Rng As Range, Ocl As Range, Cl As Range
Dim Found As Boolean
Set oDat = Sheets("Data1").Range(Sheets("Data1").Range("A4"), Sheets("Data1").Range("A" & Rows.Count).End(xlUp))
Set Rng = Range(Range("A3"), Range("A9").End(xlUp))

For Each Cl In Rng
    Found = False
    For Each Ocl In oDat
        If Cl.Value = Ocl.Value Then
             Cl.Offset(, 1).Value = Ocl.Offset(, 1).Value
                Found = True
            End If
        Next Ocl
            If Found = False Then
                 Cl.Offset(1, 1).Value = ClearContents
                End If
        Next Cl

End Sub

Book1.xlsm

btw, sorry for the english.



Thank you.

[Kooper]

Bump. .

[dilipandey]

Hi Kooper,

I have checked your code and you are not using vlookup (or any other kind of functionality) in your code.
Why don't you use the code I provided you in post#4 ?

Regards,
DILIPandey

<click on below 'star' if this helps>

[Kooper]

Hi DILIPandey,

Yes its not like vlookup but its the function is the same as vlookup . Anyway, is there a way to revised this code?


Thanks,

Kooper

[dilipandey]

Okay.. what if one day the order of Agent No. and Material codes interchanges i.e., lets say agent number changes :-
instead of
A
B
C
D

it become

A
C
B
D



Regarding any way to revise, I am afraid I would be able to do it as I like to simplify and make the things dynamic for future usage

Regarding,
DILIPandey

<click on below 'star' if this helps>

[Kooper]

Hi DILIPandey,

Thank you.

[dilipandey]

Cheers

Suggest you to mark this thread as [SOLVED]

Regards,
DILIPandey

<click on below 'star' if this helps>

[Cutter]

@Kooper

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself.
Thanks.