UDT copying a array to worksheet


G

Guest

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
 
Ad

Advertisements

T

trevosef

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
 
Ad

Advertisements

G

Guest

Thanks for the speedy response, it works!!
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top