98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft VBA Training and help » Loop a Formula
Loop a Formula
Resolved · Medium Priority · Version 2007
Mon 29 Jul 2013, 09:52Delegate Gurkamal said...
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"
Mon 29 Jul 2013, 10:11Trainer Gary said...
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
Mon 29 Jul 2013, 10:33Delegate Gurkamal said...
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
Mon 29 Jul 2013, 10:41Trainer Gary said...
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
Mon 29 Jul 2013, 11:14Delegate Gurkamal said...
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 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
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
VBA tip:Suspend DisplayAlerts in VBATo 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: |
