vba error
 

Please help, I need this code to work.

The msgbox is fine but I need it on paer and in other
cells but it will not copy

Only the copy command fails, the other code works ok.


Sub Macro1()

'cell c6 contains the text "This bit"
myvar = Range("C6").Text
Selection.Copy
mytext = myvar & " Works " & Trim$(215) & " days each year"
MsgBox mytext

' I want to copy the string mytext into clipboard
mytext.Copy
' but that command fails

Range("D15").Select
ActiveSheet.Paste

End Sub

vba error
 

Hi
you can't use the copy method on string variables. Allowed are only
objects (as range or worksheet objects)

--
Regards
Frank Kabel
Frankfurt, Germany


Tom wrote:
Please help, I need this code to work.

The msgbox is fine but I need it on paer and in other
cells but it will not copy

Only the copy command fails, the other code works ok.


Sub Macro1()

'cell c6 contains the text "This bit"
myvar = Range("C6").Text
Selection.Copy
mytext = myvar & " Works " & Trim$(215) & " days each year"
MsgBox mytext

' I want to copy the string mytext into clipboard
mytext.Copy
' but that command fails

Range("D15").Select
ActiveSheet.Paste

End Sub

vba error
 

Tom

why copy and paste ?

Range("D15") = mytext ' should do what you want

A string doesn't have a copy method AFAIK.

Regards

Trevor


"Tom" wrote in message
...
Please help, I need this code to work.

The msgbox is fine but I need it on paer and in other
cells but it will not copy

Only the copy command fails, the other code works ok.


Sub Macro1()

'cell c6 contains the text "This bit"
myvar = Range("C6").Text
Selection.Copy
mytext = myvar & " Works " & Trim$(215) & " days each year"
MsgBox mytext

' I want to copy the string mytext into clipboard
mytext.Copy
' but that command fails

Range("D15").Select
ActiveSheet.Paste

End Sub

vba error
 

-----Original Message-----
Tom

why copy and paste ?

what I need is to make a load of calculations and produce
a piece of text based on some fields in an Excel sheet. I
need to store this as a Macro so that it runs on the sheet
(which is replaced every hour) and the user can get a
report on the current figures.

I have it working as a msgbox, but I need to print it so I
thought the best way/ only way, was to stick it in a cell
and print that range. Due to network restricions I can not
use a VB program so it needs to ba an Excel macro.

It is not as simple as "the contents of one cell" into
another, it is "the result of some code" going into a cell.

If you can think of a way to print the contents of amsgbox
I'd be as happy as a pig in that stuff that pigs are happy
in.

vba error
 

Hi

Sub Macro1()
Dim myvar As String, mytext As String
'cell c6 contains the text "This bit"
myvar = Range("C6").Text
mytext = myvar & " Works " & Trim$(215) & " days each year"
Range("D15").Value = mytext
End Sub

This is the implemented & tested solution that Trevor gave you. Test run
before assuming that your helpers misunderstood everything.

However, you are using the spreadsheet just as a mean to get to the printer
? You could use a userform instead. In the VB editor, Insert Userform. Put
a single Label onto it -it should by default be named Label1. Then run this:

Sub Macro2()
Dim myvar As String, mytext As String
'cell c6 contains the text "This bit"
myvar = Range("C6").Text
mytext = myvar & " Works " & Trim$(215) & " days each year"
UserForm1.Label1.Caption = mytext
DoEvents
UserForm1.PrintForm
DoEvents
Unload UserForm1
End Sub

HTH. Best wishes Harald

skrev i melding
...

-----Original Message-----
Tom

why copy and paste ?

what I need is to make a load of calculations and produce
a piece of text based on some fields in an Excel sheet. I
need to store this as a Macro so that it runs on the sheet
(which is replaced every hour) and the user can get a
report on the current figures.

I have it working as a msgbox, but I need to print it so I
thought the best way/ only way, was to stick it in a cell
and print that range. Due to network restricions I can not
use a VB program so it needs to ba an Excel macro.

It is not as simple as "the contents of one cell" into
another, it is "the result of some code" going into a cell.

If you can think of a way to print the contents of amsgbox
I'd be as happy as a pig in that stuff that pigs are happy
in.

I am an Arse.
 

Trevor & Harald,

Thank you both very much.

This works for me and I am so sorry I did not fully
explore the solution before reposting. I could make
excuses like it was late and I thought I would try to
scrape up a response while I was sleeping, but I should
have tested it before posting anything else.

I am now exploring the use of the Userform. This is
driving me nuts as I do all my stuff in VB6 but this
problem has to be resolved in Excel. I may well be back to
ask more questions later.

Thanks again for your assistance and patience.

Tom

vba error
 

Enter the following formula in cell "D15":
=C6 & " Works 215 days each year"

This technique also works well when you need to display messages on a chart,
since Excel won't let you build formulas on a chart (a string must simply be
a reference to a single cell on a worksheet somewhere).
--
Regards,
Bill