How to put range into redimed array?

[jaryszek]

Hi,

i have code like here:

Sub test()

  Dim a() As String
  
  ReDim a(1 To 6, 1 To 5)
  
  Dim rng As Range
  
  Set rng = ActiveSheet.Range("A2:E7")
  
  a = rng

End Sub

which throws an error about type.

It is possible to assign to string array range?
Or i have to loop?

the purpose to have array as string it to not loose leading zeros while outputting variant array.

Thanks!,
Best,
Jacek

[romperstomper]

You have to loop.

[Marc L]

Hi,

as it has no sense to allocate the ranges values to a dimed array - like allocating an useless Range variable as well ! - but
if you really need it then yes just use a loop …

[beyond Excel]

... It is possible to assign to string array range? ...

Hello. Two equivalents to what you've tried but didn't work for you would be:

Sub test1()
Dim a
Dim rng As Range

Set rng = ActiveSheet.Range("A2:E7")
a = rng
End Sub

Sub test2()
Dim a
a = ActiveSheet.Range("A2:E7")
End Sub

[romperstomper]

Neither of those approaches will give you a String array though.

[torachan]

run your 'test' macro in attached file.

Sub test()
Dim a() As Variant
a = Sheet1.Range("A2:E8").Value2
[H2] = a(1, 1): [I2] = a(6, 5): [J2] = a(7, 5)
End Sub

[jindon]

Hi,
the purpose to have array as string it to not loose leading zeros while outputting variant array.

Sub test()
    Dim a
    With [a1:e7]
        a = Evaluate("if(left(" & .Address & ",1)=""0"",""'"","""")&" & .Address)
        [g1].Resize(.Rows.Count, .Columns.Count) = a
    End With
End Sub

[jaryszek]

Wow thank you Guys!!

Awesome i am taking your ideas to my project!

Best,
Jacek

[beyond Excel]

Wow thank you Guys!! Awesome i am taking your ideas to my project!
Best, Jacek

Thanks for the +rep.

[torachan]

@jaryszek, thanks for the feedback and added rep point.