VBA Array values using IF on another array

[gt213]

I have an Array (arr3) as follows:

ReDim arr3(0 To c)
For i = LBound(arr3) To UBound(arr3)
arr3(i) = ws1.Cells(i + 4, 19) - ws1.Cells(i + 4, 13)
Next i

I want to create another Array (also (0 To c) where each value (i) is conditional on value (i) in Array 3.

Assuming arr3 was in cells W5:W201 and Array 4 was going to col X then in X5 the formula would be "=IF(W5>=0,COUNTIFS($W$5:$W$201,">="&0,$W$5:$W$201,">="&W5),"")"

However I don't want the value in the sheet, just stored as an Array.

Thanks,

 

[Rick Rothstein]

I have an Array (arr3) as follows:

ReDim arr3(0 To c)
For i = LBound(arr3) To UBound(arr3)
arr3(i) = ws1.Cells(i + 4, 19) - ws1.Cells(i + 4, 13)
Next i

I want to create another Array (also (0 To c) where each value (i) is conditional on value (i) in Array 3.

Assuming arr3 was in cells W5:W201 and Array 4 was going to col X then in X5 the formula would be "=IF(W5>=0,COUNTIFS($W$5:$W$201,">="&0,$W$5:$W$201,">="&W5),"")"

However I don't want the value in the sheet, just stored as an Array.

First off, assuming the c variable has already had a value assigned to it, if you declare arr3 as a plain Variant variable (no parentheses), then you can load it up using this single line of code...

[table="width: 500"]
[tr]
	[td]arr3 = Evaluate("TRANSPOSE(S4:S" & C + 4 & "-M4:M" & C + 4 & ")")[/td]
[/tr]
[/table]

Then you can ReDim the arr4 array and assign values to it this way...

[table="width: 500"]
[tr]
	[td]  ReDim arr4(1 To UBound(arr3))
  For N = 1 To UBound(arr3)
    If arr3(N) >= 0 Then
      For Each V In arr3
        If arr3(N) > V Then arr4(N) = arr4(N) + 1
      Next
    End If
  Next[/td]
[/tr]
[/table]

Assuming you Dim all of your variable, here is the declaration statement to do it for the variables I have introduced above...

[table="width: 500"]
[tr]
	[td]Dim N As Long, V As Variant, arr3 As Variant, arr4 As Variant[/td]
[/tr]
[/table]

Note: When loading up a Variant variable with an Evaluate function call like I show above, the lower bound of the created array will always be 1 not matter what the Option Base setting is.

 

[gt213]

thanks Rick, i will give that a try.