VBA Search Value from other sheet and return data

[tbxor]

Test.xlsm
	A	B	C	D	E	F	G
1	12/18/2022	16:31:51	Host WWID	Guest Badge ID	Guest Name	Contact Number	Badge No
2	12/18/2022	16:04:47	12120088	13465789	John	1234567	KK5-1
3	12/18/2022	16:05:01	12128800	13245646	Doe	1324658	KK5-2
4	12/18/2022	16:05:45	12167778	13244587	Lucy	1456738	KK5-3
5	12/18/2022	16:05:54	12167778	56478974	Aaron	1897654
6	12/18/2022	16:05:59	12778888	13264894	Joseph	1348795
7	12/18/2022	16:06:05	12778888	14654987	Isaac	1354879
Dec22

Test.xlsm
	A	B	C
1	No	Guest Badge ID	Origin
2	1	13465789	KK5
3	2	13245646	KK5
4	3	13244587	KK5
5	4	56478974	KK5
6	1	13264894	KD2
7	2	14654987	KD2
BadgeList

There's multiple guest badge origin. KK5 and KD2. All of them listed in badge list sheet. I need to lookup the value of Guest Badge ID, 5th column in Dec22 sheet. Get the data from Badge List sheet and set the value of Badge No column in Dec22 sheet to be a combination of Origin and Badge No as shown in G2 until G4.

 

[Alex Blakenburg]

Is this what you mean ?

Book2
	A	B	C	D	E	F	G
1	12/18/2022	16:31:51	Host WWID	Guest Badge ID	Guest Name	Contact Number	Badge No
2	18/12/2022	0.669988	12120088	13465789	John	1234567	KK5-1
3	18/12/2022	0.67015	12128800	13245646	Doe	1324658	KK5-2
4	18/12/2022	0.67066	12167778	13244587	Lucy	1456738	KK5-3
5	18/12/2022	0.670764	12167778	56478974	Aaron	1897654	KK5-4
6	18/12/2022	0.670822	12778888	13264894	Joseph	1348795	KD2-1
7	18/12/2022	0.670891	12778888	14654987	Isaac	1354879	KD2-2
Dec22

Cell Formulas
Range	Formula
G2:G7	G2	=XLOOKUP(D2, BadgeList!$B$2:$B$100, BadgeList!$C$2:$C$100 & "-" & BadgeList!$A$2:$A$100)

 

[tbxor]

Is this what you mean ?

Book2
	A	B	C	D	E	F	G
1	12/18/2022	16:31:51	Host WWID	Guest Badge ID	Guest Name	Contact Number	Badge No
2	18/12/2022	0.669988	12120088	13465789	John	1234567	KK5-1
3	18/12/2022	0.67015	12128800	13245646	Doe	1324658	KK5-2
4	18/12/2022	0.67066	12167778	13244587	Lucy	1456738	KK5-3
5	18/12/2022	0.670764	12167778	56478974	Aaron	1897654	KK5-4
6	18/12/2022	0.670822	12778888	13264894	Joseph	1348795	KD2-1
7	18/12/2022	0.670891	12778888	14654987	Isaac	1354879	KD2-2
Dec22

Cell Formulas
Range	Formula
G2:G7	G2	=XLOOKUP(D2, BadgeList!$B$2:$B$100, BadgeList!$C$2:$C$100 & "-" & BadgeList!$A$2:$A$100)

it does the trick but I'll need to use VBA since my colleagues are mostly old people with low general knowledge of excel

 

[Alex Blakenburg]

How does VBA help ? I would imagine that would make it even harder for your audience.

 

[tbxor]

How does VBA help ? I would imagine that would make it even harder for your audience.

It'll be helpul since the VBA code is not visible on front end. They can't tamper the cell formula that way.

 

[Alex Blakenburg]

OK give this a try:

Sub LookupBadgeID()

    Dim shtAct As Worksheet, shtBadge As Worksheet
    Dim rngFormula As Range, rngBadge As Range
    Dim LRowAct As Long, LRowBadge As Long
    Dim strFormula As String
    
    
    Set shtAct = ActiveSheet
    Set shtBadge = Worksheets("BadgeList")
    
    With shtAct
        LRowAct = .Range("A" & Rows.Count).End(xlUp).Row
        Set rngFormula = .Range(.Cells(2, "G"), .Cells(LRowAct, "G"))
    End With
    
    With shtBadge
        LRowBadge = .Range("A" & Rows.Count).End(xlUp).Row
        Set rngBadge = .Range(.Cells(2, "A"), .Cells(LRowBadge, "C"))
    End With

    strFormula = "=XLOOKUP(D2, BadgeList!$B$2:$B$~LR, BadgeList!$C$2:$C$~LR & "" - "" & BadgeList!$A$2:$A$~LR)"
    strFormula = Replace(strFormula, "~LR", LRowBadge)
    
    rngFormula.Formula2 = strFormula
    rngFormula.Value2 = rngFormula.Value2

End Sub

 

[tbxor]

the lookup code took quite a few seconds to be completed. Amending the data of the cells involved in lookup range will also loads a bit more after cell change.

 

[Alex Blakenburg]

Amending the data of the cells involved in lookup range will also loads a bit more after cell change.

What does that mean ?

How many rows of data are we talking ? It would need to be rewritten using a dictionary to speed it up. I wouldn't be able to look at that until much later today.
(I am in Australia)

 

[tbxor]

What does that mean ?

How many rows of data are we talking ? It would need to be rewritten using a dictionary to speed it up. I wouldn't be able to look at that until much later today.
(I am in Australia)

this is the reply for earlier xlookup in front end. not vba. what i meant is. when i add a new badge in badge list sheet, it'll load around 2 3 secs.

 

[tbxor]

OK give this a try:

Sub LookupBadgeID()

    Dim shtAct As Worksheet, shtBadge As Worksheet
    Dim rngFormula As Range, rngBadge As Range
    Dim LRowAct As Long, LRowBadge As Long
    Dim strFormula As String
   
   
    Set shtAct = ActiveSheet
    Set shtBadge = Worksheets("BadgeList")
   
    With shtAct
        LRowAct = .Range("A" & Rows.Count).End(xlUp).Row
        Set rngFormula = .Range(.Cells(2, "G"), .Cells(LRowAct, "G"))
    End With
   
    With shtBadge
        LRowBadge = .Range("A" & Rows.Count).End(xlUp).Row
        Set rngBadge = .Range(.Cells(2, "A"), .Cells(LRowBadge, "C"))
    End With

    strFormula = "=XLOOKUP(D2, BadgeList!$B$2:$B$~LR, BadgeList!$C$2:$C$~LR & "" - "" & BadgeList!$A$2:$A$~LR)"
    strFormula = Replace(strFormula, "~LR", LRowBadge)
   
    rngFormula.Formula2 = strFormula
    rngFormula.Value2 = rngFormula.Value2

End Sub

This will also take a while to complete since it checks all row in column G. I think maybe just check for guest badge number whenever I edit column D in Dec22 sheet.