how to append a phrase to a cellcontent

[sumesh56]

i want to add a phrase before a word in a cell. is it possible? kindl
see the attached file.thank

+-------------------------------------------------------------------
|Filename: how to append a phrase.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=964
+-------------------------------------------------------------------

 

[Claus Busch]

Hi,

Am Wed, 2 Apr 2014 12:22:11 +0100 schrieb sumesh56:

i want to add a phrase before a word in a cell. is it possible? kindly
see the attached file.thanks

try:

Sub Test()
Dim LRow As Long
Dim myStr As String
Dim rngC As Range

With ActiveSheet
LRow = .Cells(Rows.Count, 3).End(xlUp).Row
myStr = .Range("E8")
For Each rngC In .Range("C4:C" & LRow)
rngC = myStr & rngC
Next
End With
End Sub


Regards
Claus B.

 

[Claus Busch]

Hi,

Am Wed, 2 Apr 2014 12:22:11 +0100 schrieb sumesh56:

i want to add a phrase before a word in a cell. is it possible? kindly
see the attached file.thanks

write e.g. in H10:
=IF(C4<>"",$E$8&C4,"")
and copy down.
Copy the range with the formulas and paste special => paste values in C4


Regards
Claus B.

 

[sumesh56]

Hi,

Am Wed, 2 Apr 2014 12:22:11 +0100 schrieb sumesh56:
-

write e.g. in H10:
=IF(C4<>"",$E$8&C4,"")
and copy down.
Copy the range with the formulas and paste special => paste values i
C4


Regards
Claus B.

Claus Busch,
thanks a lot for the macro. it works

perfect. can you find time to explain

what you meant by Rows,count 3?

I want to learn it so that i can use it

in different situations. I would like to

know how would you edit your macro to

suit to the new file attached.have a nice

day.

I would like to see that after merging

the strings, i would delete Mystring and

the result should maintain.
have a nice day

+-------------------------------------------------------------------
|Filename: extended cellsappend.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=965
+-------------------------------------------------------------------

 

[sumesh56]

Hi,

Am Wed, 2 Apr 2014 17:27:14 +0100 schrieb sumesh56:
-

..Cells(rows.count,3).end(xlup).row
shows you the last row with values in column 3 (C)
For the range to look in you have to do no changes.
But you have to change E8 to G1.
-

in G1 you have no trailing space behind your phrase. So you have to
insert the space with the code.
Try:

Sub Test()
Dim LRow As Long
Dim myStr As String
Dim rngC As Range

With ActiveSheet
LRow = .Cells(Rows.Count, 3).End(xlUp).Row
myStr = .Range("G1")
For Each rngC In .Range("C4:C" & LRow)
rngC = myStr & " " & rngC
Next
.Range("G1").ClearContents
End With
End Sub


Regards
Claus B.

Claus Busch,
thank you very much for the help. Your macro is perfect in all respect
have a nice day

+-------------------------------------------------------------------
+-------------------------------------------------------------------