Issue with looping an if function through an array.

Will S

Board Regular
Joined
Oct 29, 2013
Messages
69
Heya, I'm certain I'm just missing something blatant and I will slap myself for not seeing it sooner but been tired of going around in circles.

I have a big macro that runs whenever there is a change made. I'm not gonna link it all because I don't think that would be useful... This macro does:

1. Disables Events.
2. Removes Protection
3.Calls some macros that check a few values and change a few cells.
4. Activates events.
5. Hides or shows ranges of cells depending on the results of the called macros.
6. Reactivates protection.

As well as the cells the called macro changes, there is a column of True/False results stretching from AE16 to AE491 with several blanks in the list as well. (All of this is decided upon a different macro that isn't linked with the change.) I want a loop that will read the True/False column and if it finds a false, make 8 buttons invisible.

I tried putting this code between Step 5 and 6 of the macro.

Code:
 Dim J As Integer
J = 16
Do
If Range("AE" & J).Text = "False" Then
Next1.Visible = False
Next2.Visible = False
Next3.Visible = False
Next4.Visible = False
Next5.Visible = False
Next6.Visible = False
Next7.Visible = False
Next8.Visible = False
End If
J = J + 1
 Exit Do
 
 Loop Until J = 492
This isn't working at all... I was thinking if there was gonna be an issue then it would be the opposite where it stops the buttons appearing all together but even when there are falses and I am changing things it doesn't seem to make a difference.

Can anyone shed some light on this?

Best regards,
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

drsarao

Well-known Member
Joined
Sep 9, 2009
Messages
1,146
Office Version
  1. 2007
Platform
  1. Windows
I think "Exit do" will end the loop after first iteration itself. Remove it and test.
The loop should make the buttons invisible if it finds even a SINGLE False in AE16:AE491.
(I hope that is what you want)
 

Will S

Board Regular
Joined
Oct 29, 2013
Messages
69
That works great, thanks. Unfortunately now the original error I was expecting is happening but that's a vast improvement, as I've already thought about how to get past that, thanks!
 

drsarao

Well-known Member
Joined
Sep 9, 2009
Messages
1,146
Office Version
  1. 2007
Platform
  1. Windows
You are welcome!
Thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,037
Messages
5,856,962
Members
431,841
Latest member
jaybeem

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