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

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

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