CODEWITHSUNDEEP
vbaexcel
Cyril
Cyril

Reputation: 6829

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

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.

Upvotes: 1

Views: 325

Answers (1)

user2520038
user2520038

Reputation: 36

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

Upvotes: 2

Related Questions