IF checking

[afrobea_r]

Dear excel wizards,

I am trying to insert a part in my current macro whereby I can return a value based on the following table condition as below.

How do I develop a Macro that calculates a value for a specific column to equal the value of general location in this table?

Basically it should generate the location value based on postal code numbers.

Need all the help I can get. Thanks!! Yan

Postal District	Postal Sector (1st 2 digits of 6-digit postal codes)	General Location
01	01, 02, 03, 04, 05, 06	Raffles Place, Cecil, Marina, People's Park
02	07, 08	Anson, Tanjong Pagar
03	14,15, 16	Queenstown, Tiong Bahru
04	09, 10	Telok Blangah, Harbourfront
05	11, 12, 13	Pasir Panjang, Hong Leong Garden, Clementi New Town
06	17	High Street, Beach Road (part)
07	18, 19	Middle Road, Golden Mile
08	20, 21	Little India
09	22, 23	Orchard, Cairnhill, River Valley
10	24, 25, 26, 27	Ardmore, Bukit Timah, Holland Road, Tanglin
11	28, 29, 30	Watten Estate, Novena, Thomson
12	31, 32, 33	Balestier, Toa Payoh, Serangoon
13	34, 35, 36, 37	Macpherson, Braddell
14	38, 39, 40, 41	Geylang, Eunos
15	42, 43, 44, 45	Katong, Joo Chiat, Amber Road
16	46, 47, 48	Bedok, Upper East Coast, Eastwood, Kew Drive
17	49, 50, 81	Loyang, Changi
18	51, 52	Tampines, Pasir Ris
19	53, 54, 55, 82	Serangoon Garden, Hougang, Ponggol
20	56, 57	Bishan, Ang Mo Kio
21	58, 59	Upper Bukit Timah, Clementi Park, Ulu Pandan
22	60, 61, 62, 63, 64	Jurong
23	65, 66, 67, 68	Hillview, Dairy Farm, Bukit Panjang, Choa Chu Kang
24	69, 70, 71	Lim Chu Kang, Tengah
25	72, 73	Kranji, Woodgrove
26	77, 78	Upper Thomson, Springleaf
27	75, 76	Yishun, Sembawang
28	79, 80	Seletar

<tbody>
</tbody>

 

[Sunny Kow]

Hi you can consider using a VLOOKUP if your postcode is in a specific column e.g. column D

=VLOOKUP("*"&LEFT(D3,2)&"*",B:C,2,FALSE)

where A:C correspond to the 3 column of your table above and the poscode is in column D.
Column B must be text for it to work so 17 must be converted to text by adding a ' in front of it.

 

[afrobea_r]

Hi Sunny,

Thanks for your response.

I am trying to figure in VBA to return the value based on general location table that is indexed to their postal sector.

For example, if a location has the following postal code, 035557, the postal sector is first 2 digits "03" which references the name of the general location (eg. anson). By recognizing the postal sector, it will return value of "anson"

 

[Sunny Kow]

Where are your postcode located in your worksheet? Where do you want the output to be?

 

[afrobea_r]

Hi Sunny,

Thanks for your reply. For example, in my main spreadsheet, I have these 2 columns.

Postal Code	Postal Location
537029	?
537029	?
537029
537029
537029
537029
537029
537029
537029
537029
537029

<colgroup><col><col></colgroup><tbody>
</tbody>

In another tab, I have the table with the postal code references as in my starting thread. How do I do a VBA looping check so that it can dynamically recognize the location based on the postal code input.

 

[Sunny Kow]

Give this a try. Change the sheet and range if necessary.

Sub Postcode()


Dim cell As Range


For Each cell In Range("D2:D12") '<==Change to refer to your postal code column
    Code = Left(cell, 2)
    Location = Application.VLookup("*" & Code & "*", Worksheets("Sheet2").Columns("B:C"), 2, False) '<==change to where your table is
    cell.Offset(, 1).Value = Location
Next


End Sub

 

[afrobea_r]

Hi Sunny,

Many thanks for this. It worked fine!

However, I encountered another issue, because that are some postal codes that start with 0, for example 067980, the postal sector (first 2 digits) will hence be 06. But excel does not recognizes the 0 and takes it as 67 instead.

Any ideas on how to work around this?

 

[Sunny Kow]

Hi afrobea_r
The postal code need to be text with leading zero's and not formatted to display zeros.
You can pad your postal codes with leading zeros with =REPT("0",6-LEN(A1))&A1
with the assumption that all postal codes are 6 digits long and in column A.

 

[afrobea_r]

Dear Sunny,

Many thanks for your help so far. Your solutions are extremely helpful. At the moment, I have developed something like this.

Sub Postal()


Sheets("Master Header").Select
'Inserting 2 Columns from J
Range("J:K").EntireColumn.Insert


Range("J2").Select
ActiveCell.FormulaR1C1 = "Postal Adj"
Range("K2").Select
ActiveCell.FormulaR1C1 = "Postal District"


Range("J3").Formula = "=REPT(""0"",6-LEN(I3))& I3"
Range("J3").AutoFill Destination:=Range("J3:J20000")


Sheets("Master Header").Select
For Each cell In Range("J:J")
Range(Range("J3"), Range("J3").End(xlDown)).Select
Code = Left(cell, 2)
Location = Application.VLookup("*" & Code & "*", Worksheets("Postal").Columns("B:C"), 2, False)
cell.Offset(, 1).Value = Location
Next




End Sub

However, i would like to seek your opinion on how I can enhance the portion of the code dynamically whereby the formula autofills down to the last row where there is a postal code entry on Range I, instead of having to edit the range all the time.

And also, I realized that with the vlookup, I may encounter a Type 13 mismatch error as there may be some bad entries under the postal code column. How can I handle this error so that the loop can skip this and move on.

 

[Sunny Kow]

You can give this a try. If a postal code is not found, it will indicate ** Postal Code Not Found ** in column K.

Sub Postal()

Sheets("Master Header").Select
'Inserting 2 Columns from J
Range("J:K").EntireColumn.Insert

Range("J2").Select
ActiveCell.FormulaR1C1 = "Postal Adj"
Range("K2").Select
ActiveCell.FormulaR1C1 = "Postal District"

Range("J3").Formula = "=REPT(""0"",6-LEN(I3))& I3"
Range("J3").AutoFill Destination:=Range("J3:J" & Range("I" & Rows.Count).End(xlUp).Row)

For Each cell In Range("J3:J20")
    If cell = "" Then Exit For
    Code = Left(cell, 2)
    Location = Application.IfError(Application.VLookup("*" & Code & "*", Worksheets("Postal").Columns("B:C"), 2, False), "** Postal Code Not Found **")
    cell.Offset(, 1).Value = Location
Next

End Sub