Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Automatic addition of Text
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 293
Default Automatic addition of Text

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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 896
Default Automatic addition of Text
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


On 17 Sty, 13:48, stew wrote:
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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 133
Default Automatic addition of Text
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

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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 896
Default Automatic addition of Text
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


On 17 Sty, 13:48, stew wrote:
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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 896
Default Automatic addition of Text
ooops! i meant ROW instead of COLUMN of course, sorry

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

On 17 Sty, 14:00, Jarek Kujawa wrote:
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

On 17 Sty, 13:48, stew wrote:



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- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 293
Default Automatic addition of Text
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
"Daniel.C" wrote:

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

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



Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 293
Default Automatic addition of Text
Dear Jarek

Thank you for your Help and Interest. I have used Daniel's Macro

Best

Stew

"Jarek Kujawa" wrote:

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


On 17 Sty, 13:48, stew wrote:
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


Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 133
Default Automatic addition of Text
Yes, unless you want columns B and AD :
If Target.Column < 30 And Target.Column < 2 Then Exit Sub
Daniel

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
"Daniel.C" wrote:

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

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





Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 22,906
Default Automatic addition of Text
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

On Sat, 17 Jan 2009 05:55:01 -0800, stew
wrote:

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
"Daniel.C" wrote:

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

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




Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 293
Default Automatic addition of Text

Hi Daniel

Sorry I need to exclude Rows 1 thru 9 from the Macro. Can You adapt

Best

Stew
"Daniel.C" wrote:

Yes, unless you want columns B and AD :
If Target.Column < 30 And Target.Column < 2 Then Exit Sub
Daniel

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
"Daniel.C" wrote:

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

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






Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 133
Default Automatic addition of Text
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

Hi Daniel

Sorry I need to exclude Rows 1 thru 9 from the Macro. Can You adapt

Best

Stew
"Daniel.C" wrote:

Yes, unless you want columns B and AD :
If Target.Column < 30 And Target.Column < 2 Then Exit Sub
Daniel

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
"Daniel.C" wrote:

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

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








Reply With QuoteReply With Quote
  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 293
Default Automatic addition of Text
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

"Daniel.C" wrote:

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

Hi Daniel

Sorry I need to exclude Rows 1 thru 9 from the Macro. Can You adapt

Best

Stew
"Daniel.C" wrote:

Yes, unless you want columns B and AD :
If Target.Column < 30 And Target.Column < 2 Then Exit Sub
Daniel

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
"Daniel.C" wrote:

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

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









Reply With QuoteReply With Quote
  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 293
Default Automatic addition of Text
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

"Daniel.C" wrote:

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

Hi Daniel

Sorry I need to exclude Rows 1 thru 9 from the Macro. Can You adapt

Best

Stew
"Daniel.C" wrote:

Yes, unless you want columns B and AD :
If Target.Column < 30 And Target.Column < 2 Then Exit Sub
Daniel

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
"Daniel.C" wrote:

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

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









Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Text color automatic Jackie Lamorie Setting up and Configuration of Excel 3 January 28th 09 07:44 PM
Automatic text Thor Excel Worksheet Functions 2 November 12th 06 02:04 PM
Addition on "text" cells ledzepe Excel Discussion (Misc queries) 2 October 27th 05 08:17 PM
Automatic Text to column dinesh Excel Discussion (Misc queries) 1 June 10th 05 02:38 PM
Automatic colors for text mogravy Excel Discussion (Misc queries) 1 April 19th 05 05:46 PM


All times are GMT +1. The time now is 09:01 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement