Add value to array based on if...then output in VBA

[Guest]

How do I add a value to an array (in VBA) based upon the output of an if...then statement? I have two arrays -- if a certain value falls within a range in array 1, I want to add the corresponding value in array 2 to a third array, which I will use to hold values for later calculation.

Thanks in advance for your help.

 

[Frank Kabel]

Hi Scott
not quite sure what you're trying to do. Could you give a more specific
example (or the code you already have)?

 

[Tom Ogilvy]

As an example:

store values from ar2 in ar3 if ar1 is >1 and < 10

Sub BBB()
Dim ar1(1 To 10), ar2(1 To 10), ar3()
ReDim ar3(1 To 1)
j = 0
For i = 1 To 10
ar1(i) = Int(Rnd() * 25 - 8)
ar2(i) = i * i
If ar1(i) > 1 And ar1(i) < 10 Then
j = j + 1
ReDim Preserve ar3(1 To j)
ar3(j) = ar2(i)
End If
Next
For i = 1 To j
Debug.Print ar3(i)
Next

End Sub

--
Regards,
Tom Ogilvy

How do I add a value to an array (in VBA) based upon the output of an

if...then statement? I have two arrays -- if a certain value falls within a
range in array 1, I want to add the corresponding value in array 2 to a
third array, which I will use to hold values for later calculation.

 

[Alan Beban]

If speed of execution is an issue, one might want to remove the ReDim
Preserve from the loop:

Sub BBB()
Dim ar1(1 To 10), ar2(1 To 10), ar3()
'ReDim ar3(1 To 1)
ReDim ar3(1 To 10)
j = 0
For i = 1 To 10
ar1(i) = Int(Rnd() * 25 - 8)
ar2(i) = i * i
If ar1(i) > 1 And ar1(i) < 10 Then
j = j + 1
'ReDim Preserve ar3(1 To j)
ar3(j) = ar2(i)
End If
Next
ReDim Preserve ar3(1 To j)
For i = 1 To j
Debug.Print ar3(i)
Next
End Sub

 

[Harlan Grove]

If speed of execution is an issue, one might want to remove the ReDim
Preserve from the loop:

....

If execution speed were critical, one might dispense with the ReDim Preserve
call entirely and use

Dim ar3(1 To 10)