Loop a Formula

Resolved · Medium Priority · Version 2007

Mon 29 Jul 2013, 09:52 Delegate 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:11 Trainer 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:33 Delegate 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:41 Trainer 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:14 Delegate 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 courses

	98.8 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

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