assign values/string to cells using array vba

Question

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

Try this Worksheets("Sheet1").Range("Z1").Resize(3,1).Value = v — John Alexiou
– John Alexiou, Commented Nov 17, 2017 at 19:14

QHarr · Accepted Answer · 2017-11-17 19:35:32Z

3

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

edited Nov 17, 2017 at 19:35

answered Nov 17, 2017 at 18:41

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

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

3 Comments

YowE3K Over a year ago

FWIW, you can make the code base-independent by using .Cells(i + 1 - LBound(v), 26) = v(i)

QHarr Over a year ago

@YowE3K very true and a likely better solution.

YowE3K Over a year ago

If the OP isn't doing any other array-type stuff (or even if they are just used to base 1), using Option Base 1 is by far the easiest solution. But sometimes using Base 0 is needed and then tricks like subtracting the lower bound come in handy.

John Alexiou · Accepted Answer · 2017-11-17 19:25:41Z

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:

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.

user8959429 · Accepted Answer · 2017-11-17 18:42:20Z

0

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

answered Nov 17, 2017 at 18:42

user8959429

4 Comments

sc1324 Over a year ago

Can you explain why we need to start with v(0)?

user8959429 Over a year ago

Its not compulsory. Its default situation in VBA. You can change it with Option Base n. Write this line of code above the module procedures. Then in each following array's LBound will set n.

YowE3K Over a year ago

Can you explain what the i loop is for?

user8959429 Over a year ago

That's not critic, not needed and not important! I tried to result code been compatible and same the original one. Who tries the original code, will find this matter easily, himself. I tried made less changes in the original code and because this matter not important and easy, I believe who writes the original question, will find this easily.

Harun24hr · Accepted Answer · 2017-11-18 08:05:00Z

0

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.

answered Nov 18, 2017 at 8:05

Harun24hr

38k6 gold badges24 silver badges47 bronze badges

Collectives™ on Stack Overflow

assign values/string to cells using array vba

4 Answers 4

3 Comments

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related