25

I am trying to write an array to a range and I have tried several ways but no matter what, I always get only the FIRST value of the array over and over again.

Here is the code:

Option Explicit

Sub test()

    ActiveWorkbook.Worksheets("Sheet1").Cells.Clear

    Dim arrayData() As Variant
    arrayData = Array("A", "B", "C", "D", "E")

    Dim rngTarget As Range
    Set rngTarget = ActiveWorkbook.Worksheets("Sheet1").Range("A1")

    'this doesn't work
    rngTarget.Resize(UBound(arrayData, 1), 1).Value = arrayData

    Dim rngTarget2 As Range
    Set rngTarget2 = ActiveWorkbook.Worksheets("Sheet1").Range(Cells(1, 5), Cells(UBound(arrayData, 1), 5))
    'this doesn't work either
    rngTarget2.Value = arrayData

End Sub

What I expect to see is:

(Col A)     (Col E)
A           A
B           B
C           C
D           D
E           E

What I actually see is:

(Col A)     (Col E)
A           A
A           A
A           A
A           A
A           A

What am I doing wrong here?

I tried to follow Chip Pearson's suggestions, as found HERE

But no luck...

Okay, so adding in the second part of this problem:

I have a 1D array with 8,061 elements that I am passing to the following function as such:

Call writeArrayData7(strTabName, arrayBucketData, 7)

Sub writeArrayData7(strSheetName As String, arrayData As Variant, intColToStart As Integer)

    Dim lngNextRow As Long
    lngNextRow = 1 ' hard-coded b/c in this instance we are just using row 1

    ' Select range for data
    Dim rngData As Range
    Set rngData = Sheets(strSheetName).Range(Cells(lngNextRow, intColToStart), Cells(lngNextRow - 1 + UBound(arrayData, 1), intColToStart))

    ' Save data to range
    Dim arrayDataTransposed As Variant
    arrayDataTransposed = Application.Transpose(arrayData)
    rngData = arrayDataTransposed

End Sub

So when I run this, the transpose function is properly converting into an:

Array(1 to 8061, 1 to 1)

The range appears to be a single column with 8,061 cells in Column G.

But I get the following error:

Run-time error '1004':
Application-defined or object-defined error

The error is thrown on the following line:

rngData = arrayDataTransposed

--- UPDATE ---

So one thing I left out of my sample code (b/c I honestly didn't think it mattered) was that the contents of my array are actually formulas. Here is the line that I'm using in the actual live code:

arrayData(i) = "=IFERROR(VLOOKUP($D" + CStr(i) + "," + strSheetName + "!$D:$F,3,FALSE),"")"

Well, what I found (with Excel Hero's help) was that the above statement didn't have the double sets of quotes required for a string, so I had to change to this instead:

arrayBucketData(i) = "=IFERROR(VLOOKUP($D" + CStr(i) + "," + strSheetName + "!$D:$F,3,FALSE),"""")"

I can chalk that up to late-night bonehead coding.

However, one other thing I learned is that when I went back to run the full code is that it took FOREVER to paste the array to the range. This would ordinarily be a very simple task and happen quickly, so I was really confused.

After much debugging, I found that the issue came down to the fact that I was turning off all the alerts/calculations/etc and when I pasted in these formulas, the strSheetName sheet was not there yet b/c I'm developing this code separate from the main file. Apparently, it throws up a dialog box when you paste the code in, but if you have all that stuff shut off, you can't see it but it REALLY slows everything down. It takes about 6mins to paste the range if those tabs are not there, and if they exist it takes seconds (maybe less). At any rate, to refine the code a bit further, I simply added a function that checks for the required sheet and if it doesn't exist, it adds the tab as a placeholder so the entire process doesn't slow to a crawl.

Thanks to everyone for their help! I hope this helps someone else down the road.

Improve this question
7
  • Your 1004 is happening here: Set rngData = Sheets(strSheetName). because you haven't qualified your Cells object to the worksheet. So, either do it all in a With block, or do Cells(lngNextRow, intColToStart).Address (using the .Address property in conjunction with all calls to Cells class. Commented Oct 16, 2015 at 2:40
  • Were you able to get it going with David's advice? Commented Oct 16, 2015 at 3:02
  • @DavidZemens - That's actually not where I'm getting the 1004. I'm getting it here: rngData = arrayDataTransposed - I changed the line you mentioned to: Set rngData = Sheets(strSheetName).Range(Cells(lngNextRow, intColToStart).Address, Cells(lngNextRow - 1 + UBound(arrayData, 1), intColToStart).Address) and I still get the error on the above line. Commented Oct 16, 2015 at 3:14
  • 1
    @gotmike Do you have any elements in the array that have text longer than 255 characters? Commented Oct 16, 2015 at 3:24
  • 1
    Any chance you can send me a small sample workbook with the problem? I can sort it quickly. My email address is: [email protected] Commented Oct 16, 2015 at 3:35

3 Answers 3

Reset to default
43

Do this:

arrayData = Array("A", "B", "C", "D", "E")

[a1].Resize(UBound(arrayData) + 1) = Application.Transpose(arrayData)

The important bit is the Transpose() function.

But it is better to work with 2D arrays from the get go if you plan on writing them to the worksheet. As long as you define them as rows in the first rank and columns in the second, then no transposition is required.

Improve this answer
Sign up to request clarification or add additional context in comments.

12 Comments

ah... so technically a 1-dimensional array is really a single row with multiple columns? wow that's weird, i would have thought since it's just one dimension AND the first dimension in a 2-dimensional array is the row, that it would be the other way around...
Yes, 1D arrays in VBA are horizontal.
Yes, 2D arrays in VBA are row major.
@DavidZemens My answer was meant to draw attention to the Transpose() function by simplifying everything else. Of course one would properly qualify the range references. There is nothing wrong with my use of Resize.
@Excel Hero Would this code portion ~[a1].Resize(UBound(arrayData))~ not print only four values on worksheet. Should it not be ~[a1].Resize(UBound(arrayData) +1)~ to print all 5 values on the worksheet.
|
13

This:

Sub test()

    ActiveWorkbook.Worksheets("Sheet1").Cells.Clear

    Dim arrayData(1 To 5, 1 To 1) As Variant
    arrayData(1, 1) = "A"
    arrayData(2, 1) = "B"
    arrayData(3, 1) = "C"
    arrayData(4, 1) = "D"
    arrayData(5, 1) = "E"

    Dim rngTarget As Range
    Set rngTarget = ActiveWorkbook.Worksheets("Sheet1").Range("A1:A5")
    rngTarget = arrayData

End Sub

will produce:

enter image description here

Improve this answer

1 Comment

I was having the same issue as the question but this solution worked for me. Is this because in the context of the spreadsheet, the first dimension are rows and the second dimension is columns, like a matrix?
3

If I may expand the accepted answer, I propose:

[a1].Resize(UBound(arrayData) - LBound(arrayData) + 1) = Application.Transpose(arrayData)

That would be the safe way. This will work no matter if you declare your array variable as:

Dim arrayData(0 to 2)

or

Dim arrayData(1 to 3)

The accepted answer works only in the second case.

The proposed way might be useful if the size of array is unknown and you declare your arrayData as:

Dim arrayData()
Improve this answer

2 Comments

Sorry, but you are wrong. My answer works in both cases.
@ExcelHero I'm probably missing something, but your answer will not work for both cases.Dim arrayData(0 To 2) Dim arrayData2(1 To 3) Debug.Print UBound(arrayData) & " " & LBound(arrayData) Debug.Print UBound(arrayData2) & " " & LBound(arrayData2)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.