concatenating strings & data from ranges without typing a formula

[ardadogan]

hi,

although i subscribed only last week, this forum has already given me a lot of ideas i will use for my project. thank you for the inspiration.

now i will have a question. i don't know if it's feasible or it's been done before - tried to find help here and on the internet but couldn't find anything. would be glad if you could direct me to a source or give me your opinion.

here's what i want to do:

let's say i have a cell labeled WEIGHT in my workbook. value of the cell is 350 at the moment. and i have a date in a cell labeled DATE. and some other cells with different labels.

i can obviously type

=WEIGHT&" kgs shipped today"

to display

350 kgs shipped today.


i want to develop something that will insert the data from this cell whenever i type a certain string in any cell in the workbook : let's say

Dear @BUYER@,
@WEIGHT@ will be shipped on @DATE@ to @DESTINATION@ on B/L @BLNUMBER@ by @CARRIER@

to display

Dear John Smith,
350 kgs will be shipped on 23/06/2007 to Leixoes on B/L 5435245 by MAERSK.

typing

="Dear "&BUYER&"
"&WEIGHT&" will be shipped on "&"DATE"&" TO "&DESTINATION&" ON B/L "&BLNUMBER&" BY "&CARRIER&"."

isn't very convenient.

-

it doesn't have to do it when i push enter - i can type it and then run a macro using a keyboard shortcut to change the text into a formula by drawing the information from appropriate fields and concetenating the strings.


any idea how i can do it?

many many thanks -


arda

[Bob Phillips]

[vba]

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False

With Target
On Error Resume Next
If InStr(LCase(.Value), "@weight@") > 0 Then
.Value = Replace(LCase(.Value), "@weight@", Me.Range("Weight").Value)
End If
If InStr(LCase(.Value), "buyer") > 0 Then
.Value = Replace(LCase(.Value), "buyer", Me.Range("Buyer").Value)
End If
'extend as required
On Error GoTo ws_exit
End With

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

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.

[ardadogan]

i really wasn't expecting a full working code - plus this quickly. thank you so much, this will be so useful for my project!

i'll have another couple of final questions:

- can i add the code to workbook module so that it works in all sheets? (i have about 20 sheets in this workbook, all producing different documents for a document set)

- i have about 40 different labels in the workbook - is there any way to use a wildcard in this code?

i.e.
anything between two @'s replaced with the value contained by the such-labeled cell?

@XXXX@ --> Range("XXXX").Value
or
@YYYY@ --> Range("YYYY").Value


many thanks,


arda

[Bob Phillips]

This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

[vba]

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sName As String
Dim iStart As Long
Dim iEnd As Long
Dim i As Long

On Error GoTo wb_exit
Application.EnableEvents = False

With Target
On Error Resume Next
iStart = Len(.Value) + 1
For i = Len(.Value) To 1 Step -1
iEnd = InStrRev(.Value, "@", iStart - 1)
If iEnd = 0 Then Exit For
iStart = InStrRev(.Value, "@", iEnd - 1)
If iStart = 0 Then Exit For
If iEnd > iStart Then
sName = Mid$(.Value, iStart + 1, iEnd - iStart - 1)
On Error Resume Next
.Value = Replace(.Value, "@" & sName & "@", Evaluate(Me.Names(sName).RefersTo))
On Error GoTo wb_exit
End If
Next i
End With

wb_exit:
Application.EnableEvents = True
End Sub
[/vba]

[ardadogan]

thank you so much for this - it's helped me so much!