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
