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

looping

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

Excel tip:

Moving between split pane sections in a spreadsheet

If you have used the split panes feature in your worksheet, use the following keyboard shortcut keys to move quickly and effortlessly between paned sections:

F6 - Move to the next pane
Shift + F6 - Move to the previous pane

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.1 secs.