VBA - Using the output from one array to return a value from another array

[Mike UK]

I have a large array of around 15,000 lines. Each output row contains two fields with a meaningless code. I have two mapping tables to translate these codes (see mocked up idea below). The mapping tables need to be contained within the VBA code rather than a separate sheet using VLookup. I could create two arrays and get the code to loop through each mapping table to replace the code with the name but with 15,000 lines within the main array is this the most efficient way? So for example output row 5623 from the main array could have store code E in one field and Code A02 but I need to replace with meaningful names.

Store	Name			Code	Fruit
A	Browns Grocers			A01	Orange
B	Tesco			A02	Apple
C	Sainsbury			A03	Banana
D	Aldi			A04	Grapes
E	Waitrose			A05	Strawberries
F	Asda			A06	Kiwi
G	Ocado			A07	Raspberries
H	Marks & Spencer

 

[Fluff]

How many stores & codes do you have?

 

[Mike UK]

How many stores & codes do you have?

Hi Fluff,
thanks for your reply. I have 15 stores and 21 codes The large array used for the output is actually 41,000 lines

 

[Fluff]

With that amount I would suggest putting the values on a sheet, that can then be pulled into 2 arrays, rather than hard coding them in VBA. It will also make it easier to maintain if there are any changes.
Is that something you can live with?

 

[Mike UK]

With that amount I would suggest putting the values on a sheet, that can then be pulled into 2 arrays, rather than hard coding them in VBA. It will also make it easier to maintain if there are any changes.
Is that something you can live with?

Thanks Fluff for the prompt response.
I can put them in a separate sheet. If I do this is it easier to use VLookup or pull the values into 2 arrays and loop through each array to replace the code? Or is there a better VBA solution rather than the loop through each of the two arrays?

 

[Fluff]

With your lists like

Book1
	A	B	C	D	E	F
1	Store	Name			Code	Fruit
2	A	Browns Grocers			A01	Orange
3	B	Tesco			A02	Apple
4	C	Sainsbury			A03	Banana
5	D	Aldi			A04	Grapes
6	E	Waitrose			A05	Strawberries
7	F	Asda			A06	Kiwi
8	G	Ocado			A07	Raspberries
9	H	Marks & Spencer
Lists

You could use something like

Sub MikeUK()
    Dim StoreAry As Variant, CodeAry As Variant
    Dim i As Long
    
    StoreAry = Sheets("Lists").Range("A1").CurrentRegion.Value2
    CodeAry = Sheets("Lists").Range("E1").CurrentRegion.Value2
    
    With Sheets("Data")
        For i = 2 To UBound(StoreAry)
            .Range("A:A").Replace StoreAry(i, 1), StoreAry(i, 2), xlWhole, , False, , False, False
        Next i
        For i = 2 To UBound(CodeAry)
            .Range("H:H").Replace CodeAry(i, 1), CodeAry(i, 2), xlWhole, , False, , False, False
        Next i
    End With
End Sub

Change sheet names & ranges to suit

 

[Mike UK]

With your lists like

Book1
	A	B	C	D	E	F
1	Store	Name			Code	Fruit
2	A	Browns Grocers			A01	Orange
3	B	Tesco			A02	Apple
4	C	Sainsbury			A03	Banana
5	D	Aldi			A04	Grapes
6	E	Waitrose			A05	Strawberries
7	F	Asda			A06	Kiwi
8	G	Ocado			A07	Raspberries
9	H	Marks & Spencer
Lists

You could use something like

Sub MikeUK()
    Dim StoreAry As Variant, CodeAry As Variant
    Dim i As Long
   
    StoreAry = Sheets("Lists").Range("A1").CurrentRegion.Value2
    CodeAry = Sheets("Lists").Range("E1").CurrentRegion.Value2
   
    With Sheets("Data")
        For i = 2 To UBound(StoreAry)
            .Range("A:A").Replace StoreAry(i, 1), StoreAry(i, 2), xlWhole, , False, , False, False
        Next i
        For i = 2 To UBound(CodeAry)
            .Range("H:H").Replace CodeAry(i, 1), CodeAry(i, 2), xlWhole, , False, , False, False
        Next i
    End With
End Sub

Change sheet names & ranges to suit

Fluff - many thanks ... that looks quicker going down each column and replacing the values. Excellent thanks for your help.

 

[Fluff]

You're welcome & thanks for the feedback.

One word of caution, if you have stores/codes like A1, A11 make sure that the A11 is higher up the table than the A1 so it gets replaced first

 

[Mike UK]

You're welcome & thanks for the feedback.

One word of caution, if you have stores/codes like A1, A11 make sure that the A11 is higher up the table than the A1 so it gets replaced first

OK Great idea, that will save it looping through the whole array. Thanks again Fluff

 

[Fluff]

It will still loop the whole array but if
A1=Tesco and A11 =Ocado
If it replaces A1 before A11 and you had data like Col A

Book1
	A	B	C
1		Should be	Result
2	A1	Tesco	Tesco
3	A11	Ocado	Tesco1
Data