Automatic addition of Text

S

stew

Hi All

If I Have a Blank B10 and add text .Can text be added automatically to the
end of that text
ie

"Hotels New York" in B10
Become
"Hotels New York (Receipt 10)"
and
"Hotels New York" in B12
Become
"Hotels New York (Receipt 12)"

Thanks For Looking

Stew
 
J

Jarek Kujawa

in C1 try

="Hotels New York (Receipt "&COLUMN(B10)&")"

then copy it and paste special as value to B10

if this solution does not suit you then you would need to use a macro
 
D

Daniel.C

Hi.
Paste the following macro in the sheet module :

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 2 Then Exit Sub
If Target <> "" Then
Application.EnableEvents = False
Target = Target & " (Receipt " & Target.Row & ")"
Application.EnableEvents = True
End If
End Sub

HTH
Daniel
 
J

Jarek Kujawa

if you have a samll dataset and may change the cells by clicking them
one-by-one then use this macro

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)
Target = Target & " (Receipt " & Target.Row & ")"
End Sub

to use it press ALT+F11 which will move you to VBA window
then double-click on ThisWorkbook
to the right you should see a window showing "(General", click on it
and select Workbook
from the rightmost window select Workbook_SheetSelectionChange
and paste Target = Target & " (Receipt " & Target.Column & ")"

in between these 2 lines

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)

End Sub

HIH
 
J

Jarek Kujawa

ooops! i meant ROW instead of COLUMN of course, sorry

="Hotels New York (Receipt "&ROW(B10)&")"
 
S

stew

Dear Daniel

Thank You, works a treat. If I wanted the same Facility to Column AD would
the macro look like this and would I just paste in under the Previous Sub?



Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 30 Then Exit Sub
If Target <> "" Then
Application.EnableEvents = False
Target = Target & " (Receipt " & Target.Row & ")"
Application.EnableEvents = True
End If
End Sub

Thanks for your Help

Stewart
 
D

Daniel.C

Yes, unless you want columns B and AD :
Dear Daniel

Thank You, works a treat. If I wanted the same Facility to Column AD would
the macro look like this and would I just paste in under the Previous Sub?



Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 30 Then Exit Sub
If Target <> "" Then
Application.EnableEvents = False
Target = Target & " (Receipt " & Target.Row & ")"
Application.EnableEvents = True
End If
End Sub

Thanks for your Help

Stewart
Click to expand...
 
G

Gord Dibben

You are allowed only one type of each event in a worksheet.

So, no you cannot paste in under previous event.


Gord Dibben MS Excel MVP
 
D

Daniel.C

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 30 Or Target.Row < 10 Then Exit Sub
If Target <> "" Then
Application.EnableEvents = False
Target = Target & " (Receipt " & Target.Row & ")"
Application.EnableEvents = True
End If
End Sub

Daniel
 
S

stew

Forgive me Daniel, I am total new to Macros
So Below will this cover Column 2 and 30 excluding Rows less than 10

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 30 Or Target.Row < 10 And Target.Column <> 2 Or
Target.Row < 10 Then Exit Sub
If Target <> "" Then
Application.EnableEvents = False
Target = Target & " (Receipt " & Target.Row & ")"
Application.EnableEvents = True
End If
End Sub
 
S

stew

Worked it out

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 30 And Target.Column <> 2 Or Target.Row < 10 Then
Exit Sub
If Target <> "" Then
Application.EnableEvents = False
Target = Target & " (Receipt Number " & Target.Row & ")"
Application.EnableEvents = True

End If
End Sub
 
Top