loop formula

Forum home » Delegate support and help forum » Microsoft VBA Training and help » Loop a Formula

Loop a Formula

resolvedResolved · Medium Priority · Version 2007

Gurkamal has attended:
Excel Intermediate course
PowerPoint Intermediate Advanced course

Loop a Formula

Hi
I am very begginer in VBA.

I would like to loop a formula.
I am instructing in B1 to select a specifc item from a list wait 3 seconds and run the macro. I have to do this 33 times.

Basically you notice in the code the only thing that changed is R6, R7, R8...I need to do it up to R33.
So surely there is a way to create a loop.


Range("B1").Select

ActiveCell.FormulaR1C1 = "=R[6]C[33]"
Application.Wait Now + TimeSerial(0, 0, 3)
Application.Run _
"AllFourMacros"


Range("B1").Select
ActiveCell.FormulaR1C1 = "=R[7]C[33]"
Application.Wait Now + TimeSerial(0, 0, 3)
Application.Run _
"AllFourMacros"

Range("B1").Select
ActiveCell.FormulaR1C1 = "=R[8]C[33]"
Application.Wait Now + TimeSerial(0, 0, 3)
Application.Run _
"AllFourMacros"

RE: Loop a Formula

Hi Gurkamal

Thanks for getting in touch. You're right, you can loop the code you have quite easily. I have a suggestion below, it's not necessarily the most efficient or future-proof code but it will get you through your issue now.

Range("b1").Select

For i = 1 To 33

ActiveCell.FormulaR1C1 = "=R[" & i & "]C[33]"
Application.Wait Now + TimeSerial(0, 0, 3)
Application.Run _
"AllFourMacros"

Next i

I have used a FOR NEXT loop to 'count' 33 times. I then use that value in your formula, concatenating the value of 'i' with your RC formula.

I hope this helps, please let me know if you have any questions.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Loop a Formula

Hi Gary

thanks. it worked. Just wondering is there a simpler way to write the code above.

All I am doing is that I have a drop down menu in cell B1 which is picking up from a list of customer (data validation) and running a report for each customer.

The customer list starts from cell AI7 to AI34, hence the "=R[6]C[33]" in my formula.

Surely there is a simpler way to write the above code where I get rid of the 'ActiveCell.FormulaR1C1' function.

I just starting learning VBA two weeks ago so I am a beginner.

Thanks

Gurkamal

RE: Loop a Formula

Hi Gurkamal

Thanks for your reply. I'm glad the code worked!

Your code is already good so that's a great place to start from. I don't have your workbook to try this on but a Do Until loop is probably a better fit. It would be something like this:

Range("AI7").Select

Do Until ActiveCell = "" 'Keep going until you hit an empty cell

Application.Wait Now + TimeSerial(0, 0, 3)
Application.Run _
"AllFourMacros"

Activecell.Offset(1, 0).Select 'Move down one cell

Loop

It's difficult to make it any more efficient without knowing what the individual macros hidden in "AllFourMacros" do, but I'm sure there's more that could be done.

You should consider going on our VBA Intro course to learn about loops and more:

https://www.stl-training.co.uk/excel-vba-2010-training-course.php

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Loop a Formula

Thanks Gary

But the main starting point is cell B1. So not sure how I can instruct in cell B1 to go to Range AI17.select.

The numbers in the financial reports are linked to cell B1 which drives the change in numbers once i change the customer.

I will look into the course and I will see if I can get my manager to agree.

Thanks for your help to date.

Gurkamal

 

Training courses

 

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

VBA tip:

Suspend DisplayAlerts in VBA

To stop Excel asking you things like "Do you want to delete this file...", use the following line of code at the beginning of the relevant VBA procedure:

Application.DisplayAlerts = False

At the end of the procedure make sure you use the following code to reactivate Display Alerts:

Application.DisplayAlerts = True

View all VBA hints and tips


Server loaded in 0.1 secs.