Inserting Cell Text into a VBA Macro String

[simonharry]

Morning All,

Happy Friday!

So I am trying to create a macro that will use a pre determined string of text and then at 3 different points, insert the contents of 3 cells on the worksheet.

So far I have the following but I have hit a wall.

Sub Macro2()
'
' Macro2 Macro
'

'
Range("A1").Select
ActiveCell.FormulaR1C1 = _
"I am part 1 of the text " &G15& " i am part two " &G16& " I am part three" &G17& " and I am the last bit."
Range("A2").Select
End Sub

As you can see, cells G15, G16 and G17 are where the text I have entered needs to be picked up from and inserted.

Any help anyone can offer would be enormously appreciated.

Thanks in advance.

 

[FormR]

Hi, happy Friday and welcome to the forum!

You could try one of these:

'If you want a the result to be a formula:
Range("A1").Formula = "=""I am part 1 of the text"" & G15 & "" i am part two "" & G16 & "" I am part three "" & G17 & "" and I am the last bit."""


'If you want the result to be fixed text:
Range("A1").Value = "I am part 1 of the text " & Range("G15") & " i am part two " & Range("G16") & " I am part three " & Range("G17") & " and I am the last bit."

 

[tygrrboi]

[COLOR=#333333]Sub DoYourselfAFavorAndDontCallItMacro2()[/COLOR]

[COLOR=#333333]Range("A1").Value = [/COLOR][COLOR=#333333]"I am part 1 of the text " & ActiveSheet.Range("G15").Value & " I am part two " & [/COLOR][COLOR=#333333]ActiveSheet.Range("G16").Value [/COLOR][COLOR=#333333]& " I am part three " & [/COLOR][COLOR=#333333]ActiveSheet.Range("G17").Value [/COLOR][COLOR=#333333]& " and I am the last bit."[/COLOR]

[COLOR=#333333]End Sub

[/COLOR]

Change ActiveSheet. to Sheets("nameofsheet"). if you are making changes to sheets that aren't in focus.




alternatively

[COLOR=#333333]Sub DoYourselfAFavorAndDontCallItMacro2()[/COLOR]

[COLOR=#333333]Range("A1").Value = [/COLOR][COLOR=#333333]"I am part 1 of the text " & [G15] & " I am part two " & [/COLOR][COLOR=#333333][G16][/COLOR][COLOR=#333333] [/COLOR][COLOR=#333333]& " I am part three " & [/COLOR][COLOR=#333333][G17][/COLOR][COLOR=#333333] [/COLOR][COLOR=#333333]& " and I am the last bit."[/COLOR]

[COLOR=#333333]End Sub

[/COLOR]

 

[simonharry]

Thanks MrExcel MVP. You sir, are a gentleman and a scholar.

 

[simonharry]

One other question if I may please, if the string of text I am using is too long for a single line in the VBA editor in Excel, how do I split it?

I've tried & _ but that isn't working.

Thanks in advance.

 

[tygrrboi]

[COLOR=#333333]Range("A1").Value = [/COLOR][COLOR=#333333]"I am part 1 of the text " & ActiveSheet.Range("G15").Value _ 
& " I am part two " & [/COLOR][COLOR=#333333]ActiveSheet.Range("G16").Value [/COLOR][COLOR=#333333]& " I am part three " & [/COLOR][COLOR=#333333]ActiveSheet.Range("G17").Value [/COLOR][COLOR=#333333]& " and I am the last bit."[/COLOR]

'Just dont put the _ inside a text string and it should work

 

[FormR]

if the string of text I am using is too long for a single line in the VBA editor in Excel, how do I split it?

When building long strings I like to do it like this:

Sub m()
Dim myText As String


myText = myText & "I am part 1 of the text " & Range("G15")
myText = myText & " i am part two " & Range("G16")
myText = myText & " I am part three " & Range("G17")
myText = myText & " and I am the last bit."


Range("A1").Value = myText


End Sub