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,
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

drsarao

Well-known Member
Joined
Sep 9, 2009
Messages
1,143
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,143
Office Version
  1. 2007
Platform
  1. Windows
You are welcome!
Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,820
Messages
5,598,301
Members
414,224
Latest member
Crazy_FC

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