Solved: Autofill a column with text

[RonMcK]

In "AutoFill rows with formula" Malcolm suggested:


Without looping
Sub FiiData()
Range("A1:Y1000").Formula = "=INDIRECT(" & """" & "Sheet2!C" _
& """" & "& (ROW()-1) * 25+COLUMN())"
End Sub


Can I use the following (as part of a sub) to populate some columns on my work sheet with constant data to be taken from Ary2()?

With Sheets(2)
i = 1
NewLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
While i <= UBound(Ary2)
'// Enter text in cells
Range(Ary2(i, 1) & "2:" & Ary2(i, 1) & _
NewLastRow).Text = Ary2(i, 2)
i = i + 1
Wend
End With
When I tried this, I received a run-time error 1004: Unable to set the Text property of the Range class. Why is that? What is this all about?

I changed this block of code to use the following While loop. This worked through 256 iterations and stopped with an error: run-time error '1004': Application-defined or object-defined error. THE FIX: Color me chagrined. Instead of the 4th column, I was populating the 4th row and got the error when I banged into the right edge of the worksheet. Arrgghhh! Swapped positions of jj and Ary2(i,1). Everything ran perfectly.
With Sheets(2)
ii = 1
NewLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
While ii <= UBound(Ary2)
jj = 2
While jj <= NewLastRow
'// Enter text in cells
' Debug.Print ii, jj, Ary2(ii, 1), DetColNum(Ary2(ii, 1)), _
Ary2(ii, 2), NewLastRow
.Cells(DetColNum(jj, Ary2(ii, 1))) = Ary2(ii, 2)
jj = jj + 1
Wend
ii = ii + 1
Wend
End With

Any suggestions? There are 350 more lines to process in the column and 3 more columns to process after this first one. And, the, 5 more worksheets to run through my process of which this is a part.
was :banghead: but now it's: :cloud9::thumb

Thanks,

[Bob Phillips]

Ron,

Text is a read-only property, you cannot write to it. Hence your first error.

If you have the values in an array, just load them directly into the range



Dim ary As Variant

ary = [{1,"a","1a";2,"b","2b";3,"c","3c"}]
Range("C5").Resize(UBound(ary, 1) - LBound(ary, 1) + 1, UBound(ary, 2) - LBound(ary, 2) + 1) = ary

[RonMcK]

So, Bob, if I do this right (and if I understand what I'm doing), this code will load my array into the first applicable row of my worksheet? What's the easiest way for me to replicate those values from the first row down through the NewLastRow?

In the alternative, Bob, given that .text is read-only, is there any reason I should not use the following to populate my ranges? With Sheets(2)
i = 1
NewLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
While i <= UBound(Ary2)
Range(Cells(2, Ary2(i, 1)), Cells(NewLastRow, Ary2(i, 1))).Formula = Ary2(i, 2)
i = i + 1
Wend
End With
Thanks!

[Bob Phillips]

There is no reason other than efficiency Ron. When you drop an array into a range, it will load both rows and columns. Even a single dimension array has a number of rows and columns, it is just 1 column.

[RonMcK]

There is no reason other than efficiency Ron. When you drop an array into a range, it will load both rows and columns. Even a single dimension array has a number of rows and columns, it is just 1 column.

Bob,

Let me recast the unanswered question in my prior msg: how do I wed the efficiency of loading the span of cells (columns B, C, D, E, and F) from the array with the efficiency of filling a column (rows 2 thru NewLastRow) with a single a single line of code?

Thanks,

[Bob Phillips]

What is in Ary2? does it have the smae number of rows as NewLastRow?

[RonMcK]

Bob,

Ary2 consists of the column letter and the text for the cells in that column; by definition entries start in row 2, if helpful I can change the first entry to column letter & "2". I can also change from a 4 x 2 array to a 2 x 4 array.
' ReDim Ary2(4, 2)
Ary2(1, 1) = "D"
Ary2(1, 2) = "SPi"
Ary2(2, 1) = "E"
Ary2(2, 2) = "Review"
Ary2(3, 1) = "F"
Ary2(3, 2) = "PC"
Ary2(4, 1) = "G"
Ary2(4, 2) = "IE7" In this particular instance, the columns in Ary2 (and, therefore, the target worksheet) are all contiguous so we can load from the array.

[Bob Phillips]

With Sheets(2)
i = 1
NewLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Cells(1, Ary2(1, 1)).Resize(NewLastRow).Value = Ary2(1, 2)
.Cells(1, Ary2(2, 1)).Resize(NewLastRow).Value = Ary2(2, 2)
.Cells(1, Ary2(3, 1)).Resize(NewLastRow).Value = Ary2(3, 2)
.Cells(1, Ary2(4, 1)).Resize(NewLastRow).Value = Ary2(4, 2)
End With

[RonMcK]

Bob,

Thank you very much!

Cheers!