Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

CAN THE NUMBER OF LOOPS BE DEPENDANT ON A NUMBER IN A CELL?

0

Hi,

    Firstly, thank you for the time & trouble that you go to in producing the excellent, easy to follow, Excel Videos.

    My question is:- in a Macro where you want to use a "Do While Loop" (or some other type of Loop), can you use the number in a Cell from another Sheet in the same Workbook to determine how many Loops the Macro performs.

   For instance, if on Sheet 1 I have a Drop Down Menu of the 26 letters of the alphabet, and I choose the 6th. letter down (F), and this puts the number '6' in Cell A1 of Sheet 1, can I use this '6' or 'Sheet1, Cell A1', in the Loop function statement in a Macro, used on Sheet 2 of the Workbook, to Loop 6 times? The Sheet 2 Macro would be triggered by a Command Button on Sheet2 to perform certain tasks, in this example, 6 times.

    Obviously, if the above is possible, which ever number between 1 and 26 is chosen, that will determine how many Loops are performed.

     I hope that's clear, and if it is possible, then I'd much appreciate a piece of Code.

   Many thanks, Freddie (in England).

Answer
Discuss

Answers

0

Yes. You could use code like you suggest, for example....

Dim i As Integer
For i = 1 To Worksheets("Sheet1").Cells(1, "A").Value
Next i

The reason why you wouldn't becomes apparent when you think one step further: what if A1 doesn't contain a suitable number? Therefore you would probably prefer code as shown below.

Dim i As Integer
Dim x As Integer
x = Worksheets("Sheet1").Cells(1, "A").Value
If x => 1 And x <= 26 Then
    For i = 1 to x
    Next i
End If
Discuss

Discussion

Hi Variatus,                     Very many thanks for your prompt reply. Your Code/s worked perfectly so I’m very grateful for your help.  P.S.  I’ll probably be after you again for some more help in the future !!!  Thanks again & keep safe, Freddie.  
Freddie Sep 1, '20 at 1:02 pm
You are welcome, Freddie. I modified the IF condition in my answer. The original code was design to be followed by Exit Sub since it reversed the statement you see there now. Surely, you must have seen my error. Thanks for not complaining -:)
Variatus (rep: 4889) Sep 1, '20 at 8:30 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login