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 Excel VBA Training and help » Looping
Looping
Resolved · Medium Priority · Version 2010
Thu 15 Mar 2018, 12:49Delegate Emma said...
Emma has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Looping
Hi, I am trying to create a macro to change the layout of cells. The number of cells it affects would change. I am able to create the initial coding to get the first line into the correct format, but struggling to do the code to loop it.
I need:
C D E F G H I
7 Measure Cost Savings CO2 Capital ROI
8 XXX £10.00 100 0.1 £25 2.5
9 ABC £12.00 120 0.1 £100 8.3
To be converted to the following layout to the right of the document somewhere (Example from Cell N6):
Measure XXX
Savings: £10.00 100 0.1 Capital Cost: £25.00 ROI (Yrs): 2.5
Measure ABC
Savings: £12.00 120 0.1 Capital Cost: £100.00 ROI (Yrs): 8.3
The number of rows would vary, so need a way of looping it.
Current coding I have is this gets me one row re-formated, but I do not know how to loop it:
Sub NoCost()
Range("N6").Activate
ActiveCell.Value = "Measure"
ActiveCell.Offset(1, 0) = "Savings:"
ActiveCell.Offset(1, 4) = "Capital Cost:"
ActiveCell.Offset(1, 6) = "ROI (Yrs):"
ActiveCell.Offset(0, 1).Value = Range("D8").Value
ActiveCell.Offset(1, 1).Value = Range("E8").Value
ActiveCell.Offset(1, 2).Value = Range("F8").Value
ActiveCell.Offset(1, 3).Value = Range("G8").Value
ActiveCell.Offset(1, 5).Value = Range("H8").Value
ActiveCell.Offset(1, 7).Value = Range("I8").Value
ActiveCell.Offset(3, 0).Activate
End Sub
Thank you!
Fri 16 Mar 2018, 11:08Trainer Jens said...
RE: Looping
Hi Emma,
Thank you for the forum question.
You will need to use variables to be able to do what you want. Variables are on our VBA Intermediate course, so I do not know if you have variable knowledge.
The code below can do the job, but you can have a problem with the code. The variable iNumRows must store the number of records you have in the source you want to convert. I am using the CurrentRegion object to get this information in the variable. I do not know how your worksheet is structured you may have more records in the currentregion than you want to convert.
If this is the issue you can manually type the number of records you want to convert. To do this, change the line:
iNumRows = Range("c7").CurrentRegion.Rows.Count - 1
To:
iNumRows=10
If you have 10 records you want to convert.
Sub NoCost()
Dim iNumRows As Integer
Dim iRowLoopCounter As Integer
iNumRows = Range("c7").CurrentRegion.Rows.Count - 1
Range("N6").Select
For iRowLoopCounter = 1 To iNumRows
ActiveCell.Offset(iRowLoopCounter - 1, 0).Value = "Measure"
ActiveCell.Offset(iRowLoopCounter, 0).Value = "Savings:"
ActiveCell.Offset(iRowLoopCounter, 4).Value = "Capital Cost:"
ActiveCell.Offset(iRowLoopCounter, 6).Value = "ROI (Yrs):"
ActiveCell.Offset(iRowLoopCounter - 1, 1).Value = Cells(iRowLoopCounter + 7, 4).Value
ActiveCell.Offset(iRowLoopCounter, 1).Value = Cells(iRowLoopCounter + 7, 5).Value
ActiveCell.Offset(iRowLoopCounter, 2).Value = Cells(iRowLoopCounter + 7, 6).Value
ActiveCell.Offset(iRowLoopCounter, 3).Value = Cells(iRowLoopCounter + 7, 7).Value
ActiveCell.Offset(iRowLoopCounter, 5).Value = Cells(iRowLoopCounter + 7, 8).Value
ActiveCell.Offset(iRowLoopCounter, 7) = Cells(iRowLoopCounter + 7, 9).Value
ActiveCell.Offset(3, 0).Activate
Next
End Sub
Kind regards
Jens Bonde
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
Fri 23 Mar 2018: Automatically marked as resolved.
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. |
Excel tip:Jumping Between Sheets in a BookPgDn and PgUp keys scrolls up and down a screen page in most applications. |
