CODEWITHSUNDEEP
vbaexcel
sc1324
sc1324

Reputation: 600

assign values/string to cells using array vba

I am trying to assign values to column z using an array but I am not getting desire results. I am testing to assign cell Z1 = A, cell Z2 = B, cell Z3 = C Right now my codes assign all 3 cells to C. I have posted my desire results below.

Sub test()

Dim ws1 As Worksheet
Dim i, j As Long
Dim v As Variant

Set ws1 = Worksheets("Sheet1")
With ws1
    v = Array("A", "B", "C")
        For i = LBound(v) To UBound(v)
            For j = 1 To 3
                Cells(j, 26).Value = v(i)
            Next j
        Next i
End With

End Sub

enter image description here

Upvotes: 0

Views: 8640

Answers (4)

Harun24hr
Harun24hr

Reputation: 36880

What about this simple code.

Private Sub cmdFill_Click()
Dim i As Integer
    For i = 1 To 26
        Cells(i, 26).Value = Chr(64 + i)
    Next i
End Sub

You can adjust 26 if you need only 3.

Upvotes: 0

QHarr
QHarr

Reputation: 84465

You can set option base 1 so use valid row references when writing values out (if array lbound was 0 you wouldn't be able to use .Cells(0, 26) = v(0) as no row 0 in the sheet.

Option Base: Used at module level to declare the default lower bound for array subscripts. Default is base 0.

Using Base 1 means can access all array elements and use same incremental variable for sheet and array i.e. can use just one long variable i.

Option Base 1
Sub test()

Dim ws1 As Worksheet
Dim i Long
Dim v As Variant

Set ws1 = Worksheets("Sheet1")

v = Array("A", "B", "C")

With ws1

        For i = LBound(v) To UBound(v)
                   .Cells(i, 26) = v(i)               
        Next i

End With

End Sub

Upvotes: 3

John Alexiou
John Alexiou

Reputation: 29244

To learn how to read and write 1D or 2D VBA arrays into cells look at the code below:

Public Sub TestArrayReadAndWrite()

    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")

    ' Set a 1D array in VBA
    ' Write the array to cells
    Dim v() As Variant
    v = Array("A", "B", "C")
    ws.Range("A1").Resize(3, 1).Value = WorksheetFunction.Transpose(v)
    ws.Range("A5").Resize(1, 3).Value = v

    ' Set a 3×3 array in VBA
    ' Write the array to cells
    Dim a() As Variant
    ReDim a(1 To 3, 1 To 3)
    a(1, 1) = "A11": a(1, 2) = "A12": a(1, 3) = "A13"
    a(2, 1) = "A21": a(2, 2) = "A22": a(2, 3) = "A13"
    a(3, 1) = "A31": a(3, 2) = "A32": a(3, 3) = "A13"

    ws.Range("C1").Resize(3, 3).Value = a

    ' Read Array 100×1 array of cells
    ' Modify the array by doubling the values
    ' Write the array back to the next column over
    Dim b() As Variant, i As Long
    b = ws.Range("G1").Resize(100, 1).Value
    For i = 1 To 100
        b(i, 1) = 2 * b(i, 1)
    Next i
    ws.Range("G1").Offset(0, 1).Resize(100, 1).Value = b
End Sub

And the result:

RES

It is a lot faster and concise to write entire arrays with one command by assigning to Range().Resize(n,m).Value = x then to loop through all the values and set them one at a time.

Upvotes: 2

user8959429
user8959429

Reputation:

Try this

Sub test()

Dim ws1 As Worksheet
Dim i, j As Long
Dim v As Variant

Set ws1 = Worksheets("Sheet1")
With ws1
    v = Array("A", "B", "C")
        For i = LBound(v) To UBound(v)
            For j = 1 To 3
                Cells(j, 26).Value = v(j - 1)
            Next j
        Next i
End With

End Sub

Upvotes: 0

Related Questions