Populating Array

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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.

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.

Many thanks Jindon.

Ah okay sorry. I meant thanks for last explanation.

Sorry where to mark as solved ?

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)