Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDT copying a array to worksheet
I am trying to populate an array of an UDT and using it to copy it to a range
on a worksheet. The last line in the code is a problem Range("d1:f3").Value = myval ' <<<<<<<< it doesn't like myval the error message displayed is 'Only user-defined types defined in public object modules can be coerced to 'or from a variant or passed to late-bound functions. In the watch window myval(1) have the correct values Private Type mymonths mnum As Integer mname As Variant mday As Integer End Type Option Base 1 Public Sub getdate() Dim x As mymonths Dim myval(3) As mymonths x.mnum = 1 x.mname = "Jan" x.mday = 31 myval(1) = x x.mnum = 2 x.mname = "Feb" x.mday = 28 myval(2) = x x.mnum = 3 x.mname = "Mar" x.mday = 31 myval(3) = x Set mthrng = Range("d1:f3") With mthrng .ClearContents End With Range("d1:f3").Value = myval Any Help would be appreciated Thanks AH |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDT copying a array to worksheet
On Oct 24, 8:06 am, Allan wrote:
I am trying to populate an array of an UDT and using it to copy it to a range on a worksheet. The last line in the code is a problem Range("d1:f3").Value = myval ' <<<<<<<< it doesn't like myval the error message displayed is 'Only user-defined types defined in public object modules can be coerced to 'or from a variant or passed to late-bound functions. In the watch window myval(1) have the correct values Private Type mymonths mnum As Integer mname As Variant mday As Integer End Type Option Base 1 Public Sub getdate() Dim x As mymonths Dim myval(3) As mymonths x.mnum = 1 x.mname = "Jan" x.mday = 31 myval(1) = x x.mnum = 2 x.mname = "Feb" x.mday = 28 myval(2) = x x.mnum = 3 x.mname = "Mar" x.mday = 31 myval(3) = x Set mthrng = Range("d1:f3") With mthrng .ClearContents End With Range("d1:f3").Value = myval Any Help would be appreciated Thanks AH Hi Allan, Replace Range("d1:f3").value = myval with the following: Dim i As Integer i = 1 Do Until i 3 Range("d" & i).Value = myval(i).mday Range("e" & i).Value = myval(i).mname Range("f" & i).Value = myval(i).mnum i = i + 1 Loop |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDT copying a array to worksheet
Thanks for the speedy response, it works!!
AH " wrote: On Oct 24, 8:06 am, Allan wrote: I am trying to populate an array of an UDT and using it to copy it to a range on a worksheet. The last line in the code is a problem Range("d1:f3").Value = myval ' <<<<<<<< it doesn't like myval the error message displayed is 'Only user-defined types defined in public object modules can be coerced to 'or from a variant or passed to late-bound functions. In the watch window myval(1) have the correct values Private Type mymonths mnum As Integer mname As Variant mday As Integer End Type Option Base 1 Public Sub getdate() Dim x As mymonths Dim myval(3) As mymonths x.mnum = 1 x.mname = "Jan" x.mday = 31 myval(1) = x x.mnum = 2 x.mname = "Feb" x.mday = 28 myval(2) = x x.mnum = 3 x.mname = "Mar" x.mday = 31 myval(3) = x Set mthrng = Range("d1:f3") With mthrng .ClearContents End With Range("d1:f3").Value = myval Any Help would be appreciated Thanks AH Hi Allan, Replace Range("d1:f3").value = myval with the following: Dim i As Integer i = 1 Do Until i 3 Range("d" & i).Value = myval(i).mday Range("e" & i).Value = myval(i).mname Range("f" & i).Value = myval(i).mnum i = i + 1 Loop |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying an Array | Excel Worksheet Functions | |||
Copying a Variant Array to a worksheet | Excel Programming | |||
Copying an Array Formula | Excel Discussion (Misc queries) | |||
(Newbie) Copying values from an array to the worksheet | Excel Programming | |||
Copying an array to a worksheet: Excel 97 vs 2000 | Excel Programming |