problem with type definition and then setting array = range

[navyguy]

Hello,

I am trying to create a type definition and then set an array equil to a range of values in an excel sheet using this type. When I try to run the code I get a compile error "invalid qualifyer". Am I trying to do something that can't be done or is my syntax wrong? Any help would be appreciated.

The code in the module is as follows;

Type Atmospheric_Type
altitude As Double
density_ratio As Double
temperature As Double
End Type

Sub test()
last_row = Range("A1&quot.End(xlDown).Row
ReDim atmospheric_data(last_row-1) As Atmospheric_Type
atmospheric_data.altitude = Range("A1&quot.End(xlDown)
End Sub

 

[xlbo]

Not so hot on these but I can see a coupla issues straight off the bat

1:
atmospheric_data is not dimmed anywhere so you'll have a hard time re-dimming
2:
Atmospheric_Type could do with being a variant
3:
Altitude is dimmed as DOUBLE
to set an array = a range, the array must be dimmed as VARIANT
4:
Range("A1&quot.End(xlDown)
does not really mean anything

do you mean
Range("A1:A" & range("A1&quot.end(xldown).row) ??

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[navyguy]

Thanks xlbo. I modified my code as you suggested.
Unfortunately I still get the compile error "invalid qualifyer".

Does anyone know if what I am trying to do is possible?

Refined code is pasted below.

Type Atmospheric_Type
altitude() As Variant
density_ratio() As Variant
temperature() As Variant
End Type

Sub test()
Dim atmospheric_data() As Atmospheric_Type

last_row = Range("TestData&quot.End(xlDown).Row

'atmospheric_data.altitude = Range("A1:A" & Range("A1&quot.End(xlDown).Row)

atmospheric_data.altitude = Range("A1:A10&quot

For i = 1 To last_row
Debug.Print atmospheric_data(i).altitude
Next i

End Sub

 

[xlbo]

Try this:

atmospheric_data.altitude() = Range("A1:A10&quot


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[navyguy]

I appreciate the suggestion but that didn't work either. I also tried atmospheric_data().altitude=Range("A1:A10&quot and it didn't work either. Still the same error.

 

[TonyJollans]

Hi navyguy,

I don't think what you are trying to do is possible.

You have Dim'd (or ReDim'd) a one-dimensional array of type atmospheric_data and you are trying to assign a two-dimensional array (a Range) somehow to pieces of it. I don't see how it can work.

Enjoy,
Tony