Hi

I need to be able to enter a text abbreviation to represent a client address
in a cell for it to return the full address in the same cell

ie

If I type MD in cell A1 it will return MacDonalds, Unit 5............etc in
the same cell

I have a database sheet which is referenced for the full address and use the
IF formula to locate the correct address from the abbreviated text.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case "MD": .Value = "McDonald, Unit 5"
Case "PE": .Value "Peters, Brick Lane"
'etc
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Danno 24/7" wrote in message
...
Hi

I need to be able to enter a text abbreviation to represent a client
address
in a cell for it to return the full address in the same cell

ie

If I type MD in cell A1 it will return MacDonalds, Unit 5............etc
in
the same cell

I have a database sheet which is referenced for the full address and use
the
IF formula to locate the correct address from the abbreviated text.

Thanks Bob

How do I enter the Database sheet cell in the line :

case "MD": .value = ???

I guess 'DBASE'!A1 rather than "text"?

"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case "MD": .Value = "McDonald, Unit 5"
Case "PE": .Value "Peters, Brick Lane"
'etc
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Danno 24/7" wrote in message
...
Hi

I need to be able to enter a text abbreviation to represent a client
address
in a cell for it to return the full address in the same cell

ie

If I type MD in cell A1 it will return MacDonalds, Unit 5............etc
in
the same cell

I have a database sheet which is referenced for the full address and use
the
IF formula to locate the correct address from the abbreviated text.

Use

Worksheets("DBASE").Range("A1").Value

Do you have the codes in that sheet as well, so you could look it all up in
one go?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Danno 24/7" wrote in message
...
Thanks Bob

How do I enter the Database sheet cell in the line :

case "MD": .value = ???

I guess 'DBASE'!A1 rather than "text"?

"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case "MD": .Value = "McDonald, Unit 5"
Case "PE": .Value "Peters, Brick Lane"
'etc
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Danno 24/7" wrote in message
...
Hi

I need to be able to enter a text abbreviation to represent a client
address
in a cell for it to return the full address in the same cell

ie

If I type MD in cell A1 it will return MacDonalds, Unit
5............etc
in
the same cell

I have a database sheet which is referenced for the full address and
use
the
IF formula to locate the correct address from the abbreviated text.

Sorry bob you'll have to expalin further.

Use

Worksheets("DBASE").Range("A1").Value

instead of

case "MD": .value = ???

or enter

case "MD": Worksheets("DBASE").Range("A1").Value

There aren't any codes in DBASE sheet.

"Bob Phillips" wrote:

Use

Worksheets("DBASE").Range("A1").Value

Do you have the codes in that sheet as well, so you could look it all up in
one go?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Danno 24/7" wrote in message
...
Thanks Bob

How do I enter the Database sheet cell in the line :

case "MD": .value = ???

I guess 'DBASE'!A1 rather than "text"?

"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case "MD": .Value = "McDonald, Unit 5"
Case "PE": .Value "Peters, Brick Lane"
'etc
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Danno 24/7" wrote in message
...
Hi

I need to be able to enter a text abbreviation to represent a client
address
in a cell for it to return the full address in the same cell

ie

If I type MD in cell A1 it will return MacDonalds, Unit
5............etc
in
the same cell

I have a database sheet which is referenced for the full address and
use
the
IF formula to locate the correct address from the abbreviated text.

which bit?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Danno 24/7" wrote in message
...
Sorry bob you'll have to expalin further.

Use

Worksheets("DBASE").Range("A1").Value

instead of

case "MD": .value = ???

or enter

case "MD": Worksheets("DBASE").Range("A1").Value

There aren't any codes in DBASE sheet.

"Bob Phillips" wrote:

Use

Worksheets("DBASE").Range("A1").Value

Do you have the codes in that sheet as well, so you could look it all up
in
one go?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Danno 24/7" wrote in message
...
Thanks Bob

How do I enter the Database sheet cell in the line :

case "MD": .value = ???

I guess 'DBASE'!A1 rather than "text"?

"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case "MD": .Value = "McDonald, Unit 5"
Case "PE": .Value "Peters, Brick Lane"
'etc
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Danno 24/7" wrote in message
...
Hi

I need to be able to enter a text abbreviation to represent a client
address
in a cell for it to return the full address in the same cell

ie

If I type MD in cell A1 it will return MacDonalds, Unit
5............etc
in
the same cell

I have a database sheet which is referenced for the full address and
use
the
IF formula to locate the correct address from the abbreviated text.

Well do I need to change the line

case "MD": .value = "MacDonald, Unit 5............."

to

Worksheets("DBASE").Range("A1").Value

or

case "MD": .value = Worksheets("DBASE").Range("A1").Value

Sorry for being a dumb ass! :D

"Bob Phillips" wrote:

which bit?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Danno 24/7" wrote in message
...
Sorry bob you'll have to expalin further.

Use

Worksheets("DBASE").Range("A1").Value

instead of

case "MD": .value = ???

or enter

case "MD": Worksheets("DBASE").Range("A1").Value

There aren't any codes in DBASE sheet.

"Bob Phillips" wrote:

Use

Worksheets("DBASE").Range("A1").Value

Do you have the codes in that sheet as well, so you could look it all up
in
one go?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Danno 24/7" wrote in message
...
Thanks Bob

How do I enter the Database sheet cell in the line :

case "MD": .value = ???

I guess 'DBASE'!A1 rather than "text"?

"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case "MD": .Value = "McDonald, Unit 5"
Case "PE": .Value "Peters, Brick Lane"
'etc
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Danno 24/7" wrote in message
...
Hi

I need to be able to enter a text abbreviation to represent a client
address
in a cell for it to return the full address in the same cell

ie

If I type MD in cell A1 it will return MacDonalds, Unit
5............etc
in
the same cell

I have a database sheet which is referenced for the full address and
use
the
IF formula to locate the correct address from the abbreviated text.