Calling a SUB within an IF statement [VBA]

[Terry_Orange]

Hi,

In a loop if I call a sub-routine it only finds the first value, then no more. If I remove the sub-routine call, then it finds all of the values. Can anyone help ?

Thank you for any help you can give me !
Nick.

--------------------
Loop:

For j = 0 To 7
If Selection.Offset(j, 0).Value2 = sorted_array_Of_Row_Names(I) Then
MsgBox (j)
add_row_to_arrays j, CounterD
CounterD = CounterD + 1
End If
Next j
-------------------

--------------------
SUB:
Sub add_row_to_arrays(ByVal row_number As Integer, ByVal no_rows As Integer)
Range("C2").Select

ReDim array1(no_rows)
array1(no_rows) = Selection.Offset(row_number, 0).Value2
MsgBox (array1(no_rows) & " " & no_rows)
End Sub
---------------------

 

[Andrew Poulsom]

Is it because your called Sub selects cell C2? Is this the same as the Selection you are working with in the calling Sub?

 

[Terry_Orange]

No,

The selection in the loop is Range("O2").Select earlier in my macro.

Basically if I remove the sub then it finds all occurrences but keep it in and only get one of each!

Nick

 

[Andrew Poulsom]

I think it is.

In your loop your Selection is cell O2 when j = 0. It then calls Sub add_row_to_arrays, which selects cell C2. When control passes back to your loop Selection is C2 not O2.

There is no need to select ranges to work with them. So in your loop you can use:

If Range("O2").Offset(j, 0).Value2 = sorted_array_Of_Row_Names(I) Then

In your called sub you can remove the line:

Range("C2").Select

and use:

array1(no_rows) = Range("C2").Offset(row_number, 0).Value2

 

[Terry_Orange]

You are right. It is the fact that the selection has changed. I had just changed it (having reread your first post) and then saw your last response. Thanks for that. Hadn't realised that I could use cells that way to call - will be especially helpful.

THANK YOU (again !)

Nick.

 

[Terry_Orange]

You are right. It is the fact that the selection has changed. I had just changed it (having reread your first post) and then saw your last response. Thanks for that. Hadn't realised that I could use cells that way to call - will be especially helpful.

THANK YOU (again !)

Nick.