Excel Macro only returns last value in an array

Question

I have a list of 36 subjects and their ages, and whether they are in the 'Control' or "Exposed" group. I am trying split this list into two based on what group they are in. The program accurately splits the groups, but only returns the last value (age of the last subject) in each group 18 times. I am pretty sure my issue is the two "For" loops back to back, but don't know how to fix it. Thank you in advance.

enter code here Dim myArrC(1 To 18) As Variant myArrC(18) = Array() Dim myArrE(1 To 18) As Variant myArrC(18) = Array() Dim Counter As Integer Dim i As Integer For Counter = 2 To 37 For i = 1 To 18 If Sheets("Participant Info").Cells(Counter, 5) = "Control" Then myArrC(i) = Sheets("Participant Info").Cells(Counter, 3).Value Else myArrE(i) = Sheets("Participant Info").Cells(Counter, 3).Value End If Next i Next Counter Dim COunter2 As Integer For COunter2 = 1 To 18 MsgBox (myArrC(COunter2)) MsgBox (myArrE(COunter2)) Next COunter2 End Sub

CDP1802 · Accepted Answer · 2025-05-02 18:30:38Z

You need an index counter for each array eg i and j.

Option Explicit Sub demo() Const SUBJECTS = 36 Dim group As String, age As Long, msg As String Dim r As Long, i As Long, j As Long Dim arC, arE ReDim arC(1 To SUBJECTS) ReDim arE(1 To SUBJECTS) With Sheets("Participant Info") For r = 2 To SUBJECTS + 1 group = .Cells(r, "E").Value age = .Cells(r, "C").Value If group = "Control" Then i = i + 1 arC(i) = age Else j = j + 1 arE(j) = age End If Next End With If i <> j Then Debug.Print i, j MsgBox "Unequal number of Control = " & i _ & " Exposed = " & j, vbCritical Exit Sub Else For i = 1 To j msg = msg & vbLf & i & " C=" & arC(i) & " E=" & arE(i) Next MsgBox msg, vbInformation End If End Sub

Collectives™ on Stack Overflow

Excel Macro only returns last value in an array

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related