For Loop and If Statement question

J

jacqui

I have written the following sub which loops to test
whether a cell is in bold font. If this is true then I've
said insert a row. My code is below and I have three
questions.
Sub format_InsertRows()

Dim lngLastRow As Long
Dim i As Long

Worksheets("data").Select

With ActiveSheet
lngLastRow = .Cells(Rows.Count, 6).End(xlUp).Row

For i = 2 To lngLastRow
If .Cells(i, 6).Font.Bold = True Then .Cells(i,
6).EntireRow.Insert
Next

End With

End Sub

(1) My if statement inserts a row in the wrong place. I'd
like a row inserted AFTER the row in bold. How do I do
that without messing up my i counter? Do I use offset?

(2) Because my if statement inserts a row before the cell
in bold the same cell is then tested again and another row
inserted. I've tried working backwards using the step -1
but this wasn't very successful. I guess this can be
resolved as part of question 1.

(3) My data in the test cell is shown in three styles
not bold (do not insert row)
bold (insert row)
bold + italic (do not insert row)

how do I ensure that a row is inserted for the bold font
cells only, ie not the ones it finds in a bold + italic
font.

Does this make sense? Any help is appreciated.
Many thanks

Jacqui
 
J

Jake Marx

Hi jacqui,

(1) You can simply add 1 to your variable i when referring to the range:

If .Cells(i, 6).Font.Bold = True Then .Cells(i + 1, 6).EntireRow.Insert

However, this will screw things up because you're stepping foward through
the rows.

(2,3) Stepping backward should work:

Sub format_InsertRows()
Dim lngLastRow As Long
Dim i As Long

With Worksheets("data")
lngLastRow = .Cells(Rows.Count, 6).End(xlUp).Row

For i = lngLastRow To 2 Step -1
If .Cells(i, 6).Font.Bold And _
Not .Cells(i, 6).Font.Italic Then
.Cells(i + 1, 6).EntireRow.Insert
End If
Next

End With
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
C

Cecilkumara Fernando

Jacqui,
Try this
Sub format_InsertRows()

Dim lngLastRow As Long
Dim i As Long

Worksheets("data").Select

With ActiveSheet
lngLastRow = .Cells(Rows.Count, 6).End(xlUp).Row

For i = lngLastRow To 2 Step -1
If .Cells(i, 6).Font.Bold _
And Not (.Cells(i, 6).Font.Italic) Then
.Cells(i + 1, 6).EntireRow.Insert
End If
Next

End With

HTH
Cecil
 
J

jacqui

Hi Jake

Thank you for your reply, the code worked a treat. I'd
overlooked the Not statement.
Thanks again.
Jacqui


-----Original Message-----
Hi jacqui,

(1) You can simply add 1 to your variable i when referring to the range:

If .Cells(i, 6).Font.Bold = True Then .Cells(i + 1, 6).EntireRow.Insert

However, this will screw things up because you're stepping foward through
the rows.

(2,3) Stepping backward should work:

Sub format_InsertRows()
Dim lngLastRow As Long
Dim i As Long

With Worksheets("data")
lngLastRow = .Cells(Rows.Count, 6).End(xlUp).Row

For i = lngLastRow To 2 Step -1
If .Cells(i, 6).Font.Bold And _
Not .Cells(i, 6).Font.Italic Then
.Cells(i + 1, 6).EntireRow.Insert
End If
Next

End With
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

I have written the following sub which loops to test
whether a cell is in bold font. If this is true then I've
said insert a row. My code is below and I have three
questions.
Sub format_InsertRows()

Dim lngLastRow As Long
Dim i As Long

Worksheets("data").Select

With ActiveSheet
lngLastRow = .Cells(Rows.Count, 6).End(xlUp).Row

For i = 2 To lngLastRow
If .Cells(i, 6).Font.Bold = True Then .Cells(i,
6).EntireRow.Insert
Next

End With

End Sub

(1) My if statement inserts a row in the wrong place. I'd
like a row inserted AFTER the row in bold. How do I do
that without messing up my i counter? Do I use offset?

(2) Because my if statement inserts a row before the cell
in bold the same cell is then tested again and another row
inserted. I've tried working backwards using the step - 1
but this wasn't very successful. I guess this can be
resolved as part of question 1.

(3) My data in the test cell is shown in three styles
not bold (do not insert row)
bold (insert row)
bold + italic (do not insert row)

how do I ensure that a row is inserted for the bold font
cells only, ie not the ones it finds in a bold + italic
font.

Does this make sense? Any help is appreciated.
Many thanks

Jacqui
Click to expand...

.
Click to expand...
 
J

Jake Marx

Jacqui,

No problem - glad to help. You can also use "variable <> True" or "variable
= False", but I find "Not variable" to be quicker to type.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Hi Jake

Thank you for your reply, the code worked a treat. I'd
overlooked the Not statement.
Thanks again.
Jacqui


-----Original Message-----
Hi jacqui,

(1) You can simply add 1 to your variable i when referring to the
range:

If .Cells(i, 6).Font.Bold = True Then .Cells(i + 1,
6).EntireRow.Insert

However, this will screw things up because you're stepping foward
through the rows.

(2,3) Stepping backward should work:

Sub format_InsertRows()
Dim lngLastRow As Long
Dim i As Long

With Worksheets("data")
lngLastRow = .Cells(Rows.Count, 6).End(xlUp).Row

For i = lngLastRow To 2 Step -1
If .Cells(i, 6).Font.Bold And _
Not .Cells(i, 6).Font.Italic Then
.Cells(i + 1, 6).EntireRow.Insert
End If
Next

End With
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

I have written the following sub which loops to test
whether a cell is in bold font. If this is true then I've
said insert a row. My code is below and I have three
questions.
Sub format_InsertRows()

Dim lngLastRow As Long
Dim i As Long

Worksheets("data").Select

With ActiveSheet
lngLastRow = .Cells(Rows.Count, 6).End(xlUp).Row

For i = 2 To lngLastRow
If .Cells(i, 6).Font.Bold = True Then .Cells(i,
6).EntireRow.Insert
Next

End With

End Sub

(1) My if statement inserts a row in the wrong place. I'd
like a row inserted AFTER the row in bold. How do I do
that without messing up my i counter? Do I use offset?

(2) Because my if statement inserts a row before the cell
in bold the same cell is then tested again and another row
inserted. I've tried working backwards using the step - 1
but this wasn't very successful. I guess this can be
resolved as part of question 1.

(3) My data in the test cell is shown in three styles
not bold (do not insert row)
bold (insert row)
bold + italic (do not insert row)

how do I ensure that a row is inserted for the bold font
cells only, ie not the ones it finds in a bold + italic
font.

Does this make sense? Any help is appreciated.
Many thanks

Jacqui
Click to expand...

.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

For Each statement 7
Conditional Formating: MAX & MIN 3
Word Removing unwanted "lines" within a MS Word 10 table 1
Code won't fill to last row in table 4
Find sometimes ignores Application.FindFormat 3
Removing unwanted "lines" within a MS Word 10 table 2
Expert eye needed 4
CONDITIONAL FORMATTING IN VB 5

Top