Populating Array

Sub FillArrayAndDisplay()

Dim size As Integer, z As Integer, i As Integer: i = 1
Dim numbers() As Integer
Dim lrow As Long
  lrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
Dim x As Integer: x = 1

Dim txt As String


On Error Resume Next

   size = Application.WorksheetFunction.CountIf(ActiveSheet.Range("b1:b10"), "jim")
   
   ReDim numbers(1 To size)
    
      
      For i = 1 To size
      
                    If i = size - 1 Then
                    Exit For
                    End If
                    
            For x = 1 To lrow
                
                
                    If Cells(x, 2) = "jim" Then
    
                    numbers(i) = Cells(x, 2).Offset(0, -1).Value
                    
                    ReDim Preserve numbers(i)
                    
                    End If
            Next x
      Next i
    
  For i = LBound(numbers) To UBound(numbers)

    
    txt = txt & numbers(i) & vbCrLf
    
  Next i
  
  MsgBox txt


End Sub

Data is in columns A and B of worksheet.
A1:A4 is filled with numbers :2,3,5,3

B1:B4 is filled with names : jim, jim,david, jim

This line of code [/CODE] numbers(i) = Cells(x, 2).Offset(0, -1).Value [/CODE]picks up the numbers in Column A
based on this statement : [/CODE] If Cells(x, 2) = "jim" Then [/CODE]

So I only want the numbers for jim to fill the array.

Unfortunately when results are displayed via the message box they are : 3,0,0.
So the array appears to be filled with the last number for Jim (3) and then two zeroes which do not correspond to Jim.

Any help solving this problem would be greatly appreciated.

Many thanks for reading

Thank you for your reply millz.

However removing the line :ReDim Preserve numbers(i)

Does not help and the output of the messagebox is the same : 3,0,0

My objective is to only fill numbers for jim into array : 2,3,3

See

Sub test()
    Dim i As Long, mySize As Long, numbers, n As Long
    mySize = Application.CountIf(Columns(2), "jim")
    ReDim numbers(1 To mySize)
    For i = 1 To Range("a" & Rows.Count).End(xlUp).Row
        If Cells(i, 2).Value = "jim" Then
            n = n + 1: numbers(n) = Cells(i, 1).Value
        End If
    Next
    MsgBox Join(numbers, vbLf)
End Sub

Sub testNoLoop()
    Dim numbers
    With Range("a1", Range("a" & Rows.Count).End(xlUp))
        numbers = Filter(.Parent.Evaluate("transpose(if(" & .Columns(2).Address & "=""jim""," & .Address & "))"), False, 0)
    End With
    MsgBox Join(numbers, vbLf)
End Sub

Thank you so much Jindon.

Both worked Superbly!

Could you please explain:


n = n + 1: numbers(n) = Cells(i, 1).Value
MsgBox Join(numbers, vbLf)


numbers = Filter(.Parent.Evaluate("transpose(if(" & .Columns(2).Address & "=""jim""," & .Address & "))"), False, 0)

ありがとう
Arigatou Gozaimashita!

1) test loop through the cells to find "jim" in column B, and when it finds increase the counter (n) and store the corresponding value to that index(n) of array(numbers)

2) Evaluate method accepts 2d array so when the formula creates array, you can store the calculated values directly.

Many thanks Jindon.

Originally Posted by jindon

1) test loop through the cells to find "jim" in column B, and when it finds increase the counter (n) and store the corresponding value to that index(n) of array(numbers)

2) Evaluate method accepts 2d array so when the formula creates array, you can store the calculated values directly.

Hi Jindon.

I wondered why my line :

If Cells(x, 2) = "jim" Then

numbers(i) = Cells(x, 2).Offset(0, -1).Value

does not work?

I thought the logic was the same as yours which works :

n = n + 1: numbers(n) = Cells(i, 1).Value
MsgBox Join(numbers, vbLf)

Now you are asking completely different question based on your thread title.

Mark this thread as solved and start a new thread for your new question.

Ah okay sorry. I meant thanks for last explanation.

Sorry where to mark as solved ?