Automatically fill in postcodes

  • Thread starter Thread starter eternal_cat via OfficeKB.com
  • Start date Start date
E

eternal_cat via OfficeKB.com

Hello Group,

Is there a way to have the postcodes for a suburb in excel automatically
filled in into the adjacent cell?
e.g. if i type new york in a1, can i get the post code automatically filled
into b1? And so on, for different towns?

Thank you!!!
 
Hi Eternal Cat,

Try:

'==================>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, rng2 As Range
Dim rcell As Range

Set rng = Range("A1:A100") '<<========= CHANGE
Set rng2 = Intersect(rng, Target)

Application.EnableEvents = False

If Not rng2 Is Nothing Then
For Each rcell In rng2.Cells
rcell.Offset(0, 1).FormulaR1C1 = _
"=VLOOKUP(RC[-1],TABLE,2,FALSE)"
Next rcell
End If

Application.EnableEvents = True

End Sub
'<<==================

Change:

Set rng = Range("A2:A100")

to reflect your data range and in

"=VLOOKUP(RC[-1],TABLE,2,FALSE)"

change TABLE to the name of your lookup table.


This is worksheet event code and should be pasted into the worksheets's code
module (not a standard module and not the workbook's ThisWorkbook module):

*******************************************
Right-click the worksheet's tab

Select 'View Code' from the menu and paste the code.

Alt-F11 to return to Excel.

The above code will insert a lookup formula in column B when an entry is
made in the stipulated column A range.

---
Regards,
Norman



eternal_cat via OfficeKB.com said:
Will vlookup fill in the postcodes automatically? It seems that I will
have
to enter a formula every time.


Norman said:
Hi Eternal Cat,

SeeVLookup in Excel help.

See also Debra Dalgleish's tutorial at:

http://www.contextures.com/xlFunctions02.html

---
Regards,
Norman
Hello Group,
Click to expand...
[quoted text clipped - 5 lines]
Thank you!!!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top