Can I store multiline text in macro and paste it as plain text?

excelos

Active Member
Joined
Sep 25, 2011
Messages
329
Hi!

Can I store a multiline text which contains formulas and text and use a macro that will activate the A1 cell and then paste the multiline text?

Thanks!
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

CoJac

New Member
Joined
Feb 14, 2019
Messages
5
This formula will store multi line text and the result of a formula.

Code:
Sub MultiLineText()


Dim x As Double


'This is where the formula you want will be ran.  The the result will be added to your "text/formula" cell
x = WorksheetFunction.Sum(Range("B1"), Range("C1"))


'Contains your text on one line and the result of you formula on a second line
Range("A1") = "Cell B1 plus cell C1 equal " & vbNewLine & x


'Widens the column so everything is visible and not wrapped
Columns("A").ColumnWidth = 22.5


End Sub
 

excelos

Active Member
Joined
Sep 25, 2011
Messages
329
This limits the text in 3 lines for A1, B1, C1?

That is not what I want.

I want to:
1) store the multiline text in a variable as simple text
2) paste that text in A1

The multiline text can be for example:
=COUNTIFS(UDE!$DO:$DO,"Forward",UDE!$G:$G,"High",UDE!$AI:$AI,"Sprint",UDE!$AC:$AC,"Approval") =COUNTIFS(UDE!$DO:$DO,"Backward",UDE!$G:$G,"High",UDE!$AI:$AI,"Sprint 1",UDE!$AC:$AC,"Approval")
=COUNTIFS(UDE!$DO:$DO,"Forward",UDE!$G:$G,"High",UDE!$AI:$AI,"Sprint",UDE!$AC:$AC,"Boarding") =COUNTIFS(UDE!$DO:$DO,"Backward",UDE!$G:$G,"High",UDE!$AI:$AI,"Sprint 1",UDE!$AC:$AC,"Boarding")


As you understand, I want to avoid converting the text into VBA R1C1 format which is a great pain for the 300 lines I want to insert in the sheet.
That's why, I want to insert the text as plain text into the clipboard and then paste it as simple text.

Any ideas?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,518
Messages
5,529,307
Members
409,862
Latest member
lbisacca
Top