# Adding to Dynamic Array based on IF Function

#### M1k3yboi92

##### New Member
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

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Stridhan

##### Well-known Member
once youve filled the array you can fill range C with something like

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

#### M1k3yboi92

##### New Member
Thanks Stridhan,

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

#### Stridhan

##### Well-known Member
what if you change it to

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

#### M1k3yboi92

##### New Member
Nah, i tried and get More Errors.

#### Stridhan

##### Well-known Member
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

Replies
3
Views
3K
Replies
3
Views
595
Replies
6
Views
2K
Replies
3
Views
416
Replies
3
Views
255

1,195,936
Messages
6,012,391
Members
441,695
Latest member
MickRobertson

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back