Create text from multiple cells/ranges

[meherenow9]

I realize the answer for this probably rather simple, but I ask you bare with me as I jump into Excel/VBA programming for the first time...

I am trying to create a VBA script that will take data from individual cells or a range of cells (ie. A1, B2, C3, D4, E5-E8) and create strings of text that it then copies to the clipboard. For example, let's say the above cell values are:

A1: 3.14159
B2: $2.00
C3: 12
D4: 5
E5: Sue
E6: Tom
E7: Billy
E8: Joe

And I want the text that is copied to the clipboard when I press a button to read:

"Tom found $2.00. Billy and Sue discovered the value of pi was 3.14159, and Joe found 17 marbles."

(17 = C3+D4)

So far, I've found that:

Please Login or Register  to view this content.

is basically what I need, but I can't seem to figure out the xxxxxx where the string is assigned to MyData.

Thanks in advance!

[royUK]

You need to build your string like this

Please Login or Register  to view this content.

[meherenow9]

Thanks!

Now, what if I wanted to add a line break? As in:

"Tom found $2.00. Billy and Sue
discovered the value of pi was
3.14159, and Joe found 17 marbles."

[meherenow9]

I tried it precisely as you said and I keep getting the following error:

Run-time error '438':
Object doesn't support this property or method

I even tried the sample input I gave above in a test sheet with precisely the code provided and got the same error. Is there a syntactical error somewhere?

[royUK]

You need a reference set in your VBA project to the Microsoft Forms 2.0 object'

In the VB Editor Tools menu slect References, scroll to find Microsoft Forms 2.0 . Select it & click OK.

Next paste this code to a Standard Module

Please Login or Register  to view this content.

[meherenow9]

Works beautifully, thanks!