VLookup within a macro?

[chimpydude]

Hi Excel geniuses,

I'm looking for a macro which will effectively do a VLOOKUP, but where the lookup array is in the macro itself. Basically I have stand alone csv file, the users of which won't have access to a shared folder in which the lookup table can be stored.

In one column I have a list of postcodes, in the next I would like to return a reference number based on these postcodes. For example, if cell A2 = SY1 1EA then cell B2 will populate with '5000147113. The macro would need to do this for all rows with data. I have about 10 different postcodes.

Is there any way of defining a lookup table in a macro?

Many thanks!

 

[Yongle]

How about using a function where each case is the value to be looked up and the value returned is the paired reference

Function GetRef(aValue As String) As String
    Select Case aValue
        Case "CA27 0AC":    GetRef = "1234567912"
        Case "SY1 1EA":     GetRef = "5000147113"
        Case "PL10 6TY":    GetRef = "9876543219"
        'etc
    End Select
End Function

Use in macro as illustrated below

Sub CallTheFunction()
    MsgBox GetRef("SY1 1EA")
        
    With Range("B2")
        .Value = GetRef(Range("A2"))
        .NumberFormat = "0"
    End With
End Sub

Or use as a formula in Excel as illustrated below

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]PostCode[/td][td]Ref[/td][td] formula in B2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]CA27 0AC[/td][td]1234567912[/td][td] =GETREF(A2)[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]

 

[chimpydude]

This works for cell B2, but how would I make this populate all cells in column B where there is data in column A? The range could be B2:B10, B2:B30 etc...

 

[Yongle]

Sub CallTheFunction()
    Dim Cel As Range, ws As Worksheet
    Set ws = ActiveSheet
    For Each Cel In ws.Range("A2", ws.Range("A" & ws.Rows.Count).End(xlUp))
        With Cel.Offset(, 1)
            .Value = GetRef(Cel.Value)
            .NumberFormat = "0"
        End With
    Next Cel
End Sub

 

[chimpydude]

Hopefully this is the last question...

The above macro works if it's in the file which holds the data, but this file needs to be a csv so the macro needs to be in the user's personal workbook. When I put the macro in the personal workbook then it doesn't return anything (although there are no errors). Is it possible to save the macro in a user's personal workbook?

 

[Yongle]

It would have been useful to have known that at the outset

Assuming that the macro is being run from a button ... and the user clicks on the button ...
- what is the macro supposed to do ?
- is there any user involvement after clicking on the button ?
- are columns A and B (as per what you asked previously) to be hardwired into the macro ?
etc

 

[chimpydude]

We recieve a csv file from a customer which includes orders and delivery postcodes. We need to reformat this to another CSV file to allow us to import into our accounting system. As part of this we need to add our customer's delivery address reference based on the postcodes in the original file. After the macro has been run, the file saves itself and the user can import into the system.

 

[Yongle]

As part of this we need to add our customer's delivery address reference based on the postcodes in the original file

Precisely what is the macro supposed to do ?
How does it find the postcodes ?
Are they always in the same column ?
Does user need to hunt for them manually ?
Could VBA simply search for (cell exactly matching a postcode) and replace with (delivery reference)
What happens if postcodes do not match ?
Where are found delivery references written to ?
etc

 

[chimpydude]

So each postcode will only have one reference associated with it which will not change. I think I'll go down the route of pulling through the postcodes into two columns and running a find and replace on one of them. Thanks for your help.

 

[Yongle]

I think you saying that you do not want to pursue this thread any further.