For next do loop issue
 

Hi,

Here the situation. I am trying to write a macro that will create a row of
percentage under the row of values. Then drop two rows, to the next blank
and repeat until there are no more rows. I have to do this to about 2000
pieces of data and would really rather not do it by hand.

here's what i have.

Application.ScreenUpdating = False

Range("B3").Select
Do
ActiveCell.EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(2, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 0))
Dim i As Integer
Dim j As Integer
For i = 3 To 401 Step 2
Range("c" & i).Select
' For j = 2 To 400 Step 2
Do
ActiveCell.FormulaR1C1 = "=r[-1]c/r2c2" : would like j to
replace 2 and actually work:
Selection.Style = "Percent"
Selection.NumberFormat = "0.00%"
ActiveCell.Offset(0, 1).Select
Loop Until IsEmpty(ActiveCell.Offset(-1, 0))

'Next j
Next i


End Sub

any advice would be appreaciated. I am new at programing in VBA so, be
gentle.

thanks,
christian

For next do loop issue
 

Maybe something like this:

ActiveCell.FormulaR1C1 = "=r[-1]c/r" & j & "c" & j


HTH,
Paul


--

"Christian Falde" <Christian wrote in
message ...
Hi,

Here the situation. I am trying to write a macro that will create a row of
percentage under the row of values. Then drop two rows, to the next blank
and repeat until there are no more rows. I have to do this to about 2000
pieces of data and would really rather not do it by hand.

here's what i have.

Application.ScreenUpdating = False

Range("B3").Select
Do
ActiveCell.EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(2, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 0))
Dim i As Integer
Dim j As Integer
For i = 3 To 401 Step 2
Range("c" & i).Select
' For j = 2 To 400 Step 2
Do
ActiveCell.FormulaR1C1 = "=r[-1]c/r2c2" : would like j to
replace 2 and actually work:
Selection.Style = "Percent"
Selection.NumberFormat = "0.00%"
ActiveCell.Offset(0, 1).Select
Loop Until IsEmpty(ActiveCell.Offset(-1, 0))

'Next j
Next i


End Sub

any advice would be appreaciated. I am new at programing in VBA so, be
gentle.

thanks,
christian

For next do loop issue
 

Try this.

Sub insertrowsd()
dim i as long
For i = Cells(Rows.Count, "b").End(xlUp).Row To 3 Step -2
Rows(i).insert
With Cells(i, "c")
.FormulaR1C1 = "=r[-1]c/r2c2"
.NumberFormat = "0.00%"
End With
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Christian Falde" <Christian wrote in
message ...
Hi,

Here the situation. I am trying to write a macro that will create a row of
percentage under the row of values. Then drop two rows, to the next blank
and repeat until there are no more rows. I have to do this to about 2000
pieces of data and would really rather not do it by hand.

here's what i have.

Application.ScreenUpdating = False

Range("B3").Select
Do
ActiveCell.EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(2, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 0))
Dim i As Integer
Dim j As Integer
For i = 3 To 401 Step 2
Range("c" & i).Select
' For j = 2 To 400 Step 2
Do
ActiveCell.FormulaR1C1 = "=r[-1]c/r2c2" : would like j to
replace 2 and actually work:
Selection.Style = "Percent"
Selection.NumberFormat = "0.00%"
ActiveCell.Offset(0, 1).Select
Loop Until IsEmpty(ActiveCell.Offset(-1, 0))

'Next j
Next i


End Sub

any advice would be appreaciated. I am new at programing in VBA so, be
gentle.

thanks,
christian

For next do loop issue
 

thank you for your responses Paul and Don.

While i didn't end up using either suggestion. Both did stir the thought
process.

here's the end result.

Sub runthis()
'
' Macro1 Macro
' Macro recorded 11/10/2008 by desneaul
'

'
Application.ScreenUpdating = False

Range("B3").Select
Do
ActiveCell.EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(2, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 0))


Dim j As Variant
Dim i As Variant
For i = 3 To 401 Step 2
For j = 3 To 17
Range("c" & i).Select
Cells(i, j) = Cells(i - 1, j) / Cells(i - 1, 2)
Next j
Row(i).Select
Selection.NumberFormat = "0.00%"

Next i
End Sub


thanks for your suggestions.
christian

For next do loop issue
 

Row(i).Select
should be rowS()

See if this gives the same result withOUT selections

Sub insertrowsd1()
Dim i As Long
Dim j As Integer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For i = Cells(Rows.Count, "c").End(xlUp).Row To 2 Step -2
Rows(i).insert
For j = 1 To 17
Cells(i, j) = Format(Cells(i - 1, j) / Cells(i - 1, 2), "0.00%")
Next j
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Christian Falde" wrote in
message ...
thank you for your responses Paul and Don.

While i didn't end up using either suggestion. Both did stir the thought
process.

here's the end result.

Sub runthis()
'
' Macro1 Macro
' Macro recorded 11/10/2008 by desneaul
'

'
Application.ScreenUpdating = False

Range("B3").Select
Do
ActiveCell.EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(2, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 0))


Dim j As Variant
Dim i As Variant
For i = 3 To 401 Step 2
For j = 3 To 17
Range("c" & i).Select
Cells(i, j) = Cells(i - 1, j) / Cells(i - 1, 2)
Next j
Row(i).Select
Selection.NumberFormat = "0.00%"

Next i
End Sub


thanks for your suggestions.
christian