Calling a SUB within an IF statement [VBA]

Terry_Orange

Board Regular
Joined
Jul 22, 2002
Messages
133
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
---------------------
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Board Regular
Joined
Jul 22, 2002
Messages
133
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Board Regular
Joined
Jul 22, 2002
Messages
133
:p

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

Board Regular
Joined
Jul 22, 2002
Messages
133
:p

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.
 

Forum statistics

Threads
1,144,293
Messages
5,723,560
Members
422,503
Latest member
aarifmahmood

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top