looping

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

• Home
• Courses
• Promotions
• Schedule
• Formats
• Our Clients

# Looping

Resolved · Medium Priority · Version 2010

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!

### 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 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.

### Jumping Between Sheets in a Book

PgDn and PgUp keys scrolls up and down a screen page in most applications.

Ctrl+PgDn and Ctrl+PgUp keys jump from one sheet in your workbook to the next, up or down through the pages.

View all Excel hints and tips