Looping

Resolved · Medium Priority · Version 2010

Thu 15 Mar 2018, 12:49 Delegate 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:08 Trainer 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 courses

	98.7 % recommend us
	More live stats...

Training information:

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

Excel tip:

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