CODEWITHSUNDEEP
vbaexcel
John von No Man
John von No Man

Reputation: 3030

Having difficulty saving VBA array to a range in Excel

I've played around with this for several hours and am no closer to a solution.

When I create an array the following way, it outputs to a range without any difficulties:

Dim Destination As Range
Set Destination = NewSheet.Range("A1")

ReDim OutArray(1 To 1, 1 To NumArrayCols) As Variant
OutArray(1, 1) = "hello"
Destination.Resize(UBound(OutArray, 1), UBound(OutArray, 2)).Value = OutArray

However, when I create an output array in the following manner, it simply pastes a big blank array onto my spreadsheet. The first section of the code is probably mostly irrelevant, but I want to include it in case I'm missing anything:

ReDim OutArray(1, 1 To NumArrayCols) As Variant
Set ThisAtt = Wells.CurrWell.FirstAttribute(Skip:=False)
k = 1
OutArray(1, k) = "UWI"
Do
    ElevOffset = 0
    Set ThisAtt = Wells.CurrWell.CurrAttribute
    If InStr(LCase(ThisAtt.Name1), "elevation") Then
        OutArray(1, k + 1) = ThisAtt.Name1
        OutArray(1, k + 2) = ""
        OutArray(1, k + 3) = ThisAtt.Name2
        OutArray(1, k + 4) = ""
        ElevOffset = ElevOffset + 2
    Else
        OutArray(1, k + 1) = ThisAtt.Name1
        OutArray(1, k + 2) = ThisAtt.Name2
    End If
    OutArray(1, k + ElevOffset + 3) = "Recommend"
    OutArray(1, k + ElevOffset + 4) = "Rec. Value"
    OutArray(1, k + ElevOffset + 5) = "Comments"
    k = k + ElevOffset + 2 + AdditionalColumns
Loop While Not (Wells.CurrWell.NextAttribute(EnableSkipping:=False) Is Nothing)

Dim Destination As Range
Set Destination = NewSheet.Range("A1")
Destination.Resize(UBound(OutArray, 1), UBound(OutArray, 2)).Value = OutArray

It's strange, because every element in OutArray, upon inspection, seems to be there. My hand-generated array works fine, but the automatically-generated array--which seems similar in almost every way--doesn't work. Anyone know why?

Upvotes: 2

Views: 1066

Answers (1)

RBarryYoung
RBarryYoung

Reputation: 56785

I suspect that it is just your REDIM statements. In your first example you have this:

ReDim OutArray(1 To 1, 1 To NumArrayCols) As Variant

but in the second example you do this:

ReDim OutArray(1, 1 To NumArrayCols) As Variant

Notice the difference? When you say ReDim A(1 To 1) both the upper and lower bounds are 1, but when you say just Redim(1) only the upper bound is 1, the lower bound is set to the default, which is zero (0). Thus the two arrays are not the same shape/size and therefore in your second case your array does not fit correctly into the Destination Range.

Upvotes: 5

Related Questions