Loop Code

I have the below code which I need to copy 23 times
Range("G5").Select
    ActiveCell.FormulaR1C1 = "='Data Dashboard'!R[1]C[1]"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "='Data Dashboard'!R[1]C[1]"
    etc
      etc

then offset by 5 and do the next piece of code 23 times

ActiveCell.Offset(5, 0).Select
    ActiveCell.FormulaR1C1 = "='Data Dashboard'!R[-26]C[3]"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "='Data Dashboard'!R[-26]C[3]"

Instead of copying this code 23 times for each can an expert show me how to loop this please.
JagwarmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamCommented:
just put your code between this


Dim x As Long
  For x = 1 To 23
    your code to be repeated 23 times
  Next x

Open in new window


Sub test()
Dim x As Long
For x = 1 To 23
Range("G5").Select
    ActiveCell.FormulaR1C1 = "='Data Dashboard'!R[1]C[1]"
    ActiveCell.Offset(x, 0).Select
    ActiveCell.FormulaR1C1 = "='Data Dashboard'!R[1]C[1]"
    Next x
    
    ActiveCell.Offset(5, 0).Select
    For x = 1 To 23
    
    ActiveCell.FormulaR1C1 = "='Data Dashboard'!R[-26]C[3]"
    ActiveCell.Offset(x, 0).Select
    ActiveCell.FormulaR1C1 = "='Data Dashboard'!R[-26]C[3]"
    Next x
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Saurabh Singh TeotiaCommented:
Jagwarman,

You don't have to apply loop here for formula here..I'm assuming you want to apply this formula in G5 to G23 and you want to apply the formula for Datadashboard H6 onwards H7 ,H8 and so on..then you can use this code...

Sub applyformula()

Range("G5:G23").Formula = "='Data Dashboard'!H6"
End Sub

Open in new window


Saurabh...
0
JagwarmanAuthor Commented:
both excellent solutions but EE forces us to put one as 'Best Solution'
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.