Adding to Dynamic Array based on IF Function

[M1k3yboi92]

Hi Everyone,

I have been searching through many Forums and Websites trying to answer this question. Some seem to show some of what i am trying to do but not all of it and i keep getting Syntax errors.

Basically i want to create an Array that is dynamic and the Values that are put into the Array are dependant on a set IF function Criteria.

I have 3 columns. for Ease we will say A, B & C.

If C has the Value "N" and A has Value Less than 2 then i want to put the Value of B into an Array.

The If Function is settled in a For Each Loop


_________________________________________

Dim Arr() as Variant

For Each Rng In Range("C3:C" & lastrow)
rw = Rng.Row
If Rng.Value = "Y" Then
Y = Y + 1
If Range("A" & rw).Value <= "2" Then
PCY = PCY + 1
End If
ElseIf Rng.Value = "N" Then
N = N + 1
If Range("A" & rw).Value <= "2" Then
PCN = PCN + 1
ReDim Preserve Arr(0 To UBound(Arr) + 1) As Variant
Arr(UBound(Arr)) = Range("B" & rw).Value
End If
End If
Next
_______________________________________________________________________

Most of the Forums / websites are showing examples of when people have already inputted Values for the array or are using a range on the Sheet. The only Range i have is that it will be in Column B but i do not know if there will be any values to put in the array or the other extreme i do not know how many will be put in the array.

Table looks similar to this,

A	B	C
2	1009	Y
3	21	N
2	32	N
2	901	Y
3	2456	N

<tbody>
</tbody>

Once the array have been built. I then need to paste the value in the array into another set of Cells. like below:

21

32

2456

<tbody>
</tbody>

any help would be really appreciated here!

Cheers,
Mike

 

[Stridhan]

once youve filled the array you can fill range C with something like

Range("C2").Resize(UBound(arr) + 1).Value = Application.Transpose(arr)

 

[M1k3yboi92]

Thanks Stridhan,

Any ideas on how to fill the Array, i keep getting Errors with the Code i pasted

 

[Stridhan]

what if you change it to

PCN = PCN + 1
ReDim Preserve Arr(1 To PCN)
Arr(PCN) = Range("B" & rw).Value

 

[M1k3yboi92]

Nah, i tried and get More Errors.

 

[Stridhan]

your array is empty so you cant use ubound(arr) when redim
use ReDim Preserve Arr(1 To PCN)

if u use 0 to PCN your first item in array will be empty