Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Loop and If Statement question
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Loop and If Statement question
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] jacqui wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Loop and If Statement question
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 "jacqui" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Thank You
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] jacqui wrote: 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Thank You
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] jacqui wrote: 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] jacqui wrote: 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
for loop with if statement | Excel Worksheet Functions | |||
Loop Statement through If Not IsEmpty Then Paste into Destination | Excel Discussion (Misc queries) | |||
On Error Resume Next (when next statement is Do Loop ...) | Excel Discussion (Misc queries) | |||
If statement - Loop? | Excel Discussion (Misc queries) | |||
Do Until loop with if statement | Excel Programming |