Automatically fill in postcodes

[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!!!

[Norman Jones]

Hi Eternal Cat,

SeeVLookup in Excel help.

See also Debra Dalgleish's tutorial at:

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

---
Regards,
Norman



"eternal_cat via OfficeKB.com" <u14645@uwe> wrote in message
news:5575b4048719c@uwe...
> 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!!!

[eternal_cat via OfficeKB.com]

Will vlookup fill in the postcodes automatically? It seems that I will have
to enter a formula every time.


Norman Jones wrote:
>Hi Eternal Cat,
>
>SeeVLookup in Excel help.
>
>See also Debra Dalgleish's tutorial at:
>
> http://www.contextures.com/xlFunctions02.html
>
>---
>Regards,
>Norman
>
>> Hello Group,
>>
>[quoted text clipped - 5 lines]
>>
>> Thank you!!!

[Norman Jones]

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" <u14645@uwe> wrote in message
news:55772c3e9f1c9@uwe...
> Will vlookup fill in the postcodes automatically? It seems that I will
> have
> to enter a formula every time.
>
>
> Norman Jones wrote:
>>Hi Eternal Cat,
>>
>>SeeVLookup in Excel help.
>>
>>See also Debra Dalgleish's tutorial at:
>>
>> http://www.contextures.com/xlFunctions02.html
>>
>>---
>>Regards,
>>Norman
>>
>>> Hello Group,
>>>
>>[quoted text clipped - 5 lines]
>>>
>>> Thank you!!!