Using array to enter to rows of a column - VBA/Excel

Question

My overall project is to have a sheet that acts as an array/repository for values to be referenced, and to provide this as part of a macro for others to use. I have other code that references this array, and in the setup macro I have a check if this sheet already exists:

Sub Detailed_Report_SS_Setup()

Application.DisplayAlerts = False

Dim ws As Worksheet
On Error Resume Next
Set ws = Sheets("Array")
On Error GoTo 0
If Not ws Is Nothing Then
Else
Sheets.Add().Name = "Array"
Populate_Array_Sheet
End If

Application.DisplayAlerts = True

End Sub

In trying to make the Populate_Array_Sheet, I attempted to manipulate some existing code I used to name sheets in a workbook. My issue is that excel is looping through my array and adding each value to every row of the column, overwriting values as it goes through the array.

I believe my issue is the bounds (i use lower and upper), though I don't know enough about this to figure out how to correct. Here is my example of the problematic code:

Sub Populate_Array_Sheet()

Dim i As Long
Dim Arf As Variant
Arf = Array("n1", "n2", "n3", ..., "n36")

For i = LBound(Arf) To UBound(Arf)
Sheets("Array").Range("A1:A36") = Arf(i)
Next i

End Sub

I'm trying to get each individual value (n1 through n36) onto its own row in the column.

user2520038 · Accepted Answer · 2015-08-27 16:41:41Z

2

you are using Range("A1:A36") you are applying the value to entire range Try this

Sub Populate_Array_Sheet()

Dim i As Long
Dim Arf As Variant
Arf = Array("n1", "n2", "n3",....., "n36")
Sheets("Array").Range("A1").Activate
For i = LBound(Arf) To UBound(Arf)
  ActiveCell.Value = Arf(i)
  ActiveCell.Offset(1, 0).Activate
Next i

End Sub

answered Aug 27, 2015 at 16:41

user2520038

363 bronze badges

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

3 Comments

Cyril Over a year ago

That did it; thank you! Not positive what the offset does to my overall process; I was under the impression that would change columns, as written, but I guess I just don't understand the concept.

user2520038 Over a year ago

to know about the offset please go through this link msdn.microsoft.com/en-us/library/office/ff840060.aspx

Cyril Over a year ago

Doh; I was looking at it via x,y rather than RC format. Thank you!

Collectives™ on Stack Overflow

Using array to enter to rows of a column - VBA/Excel

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related