Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop macro error
Hi,
I am trying to write a macro that will loop through my spreadsheet until there is no more data. I want it to start at row 7, then colour the row (or just cells A to AD) to colour 35. I have written the macro below, however am coming up with an error that states there is no Do when trying to run it. Can anyone help? Range("7:7").Select 'Set range to start point Do While ActiveCell.Value < "" With Selection.Interior.ColorIndex = 35 ActiveRange.Offset(6, 0).Select Loop Thanks Kirsty |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop macro error
I might be able to help, but you need more to your code to prevent an
endless loop. For starters... to get you out of error mode, here is the fixes for what you have posted. Range("7:7").Select 'Set range to start point Do While ActiveCell.Value < "" With Selection.Interior.ColorIndex = 35 End With ' to end the With loop ActiveRange.Offset(6, 0).Select Wend ' to end the While loop Loop .... but this creates an endless loop. You need something inside your WHILE loop to offset this endless state. What exactly are you trying to accomplish? Do you want all rows on each sheet to color 35? Or do you want just row 7 on each sheet to color 35? Please explain in further detail. Mark Ivey "Kirsty" wrote in message ... Hi, I am trying to write a macro that will loop through my spreadsheet until there is no more data. I want it to start at row 7, then colour the row (or just cells A to AD) to colour 35. I have written the macro below, however am coming up with an error that states there is no Do when trying to run it. Can anyone help? Range("7:7").Select 'Set range to start point Do While ActiveCell.Value < "" With Selection.Interior.ColorIndex = 35 ActiveRange.Offset(6, 0).Select Loop Thanks Kirsty |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop macro error
Delete "With" or add "End With" something like below
One more thing, there is no objects or property like ActiveRange, i think this should be ActiveCell. Range("7:7").Select 'Set range to start point Do While ActiveCell.Value < "" With Selection .Interior.ColorIndex = 35 ActiveCell.Offset(6, 0).Select End With Loop keiji "Kirsty" wrote in message ... Hi, I am trying to write a macro that will loop through my spreadsheet until there is no more data. I want it to start at row 7, then colour the row (or just cells A to AD) to colour 35. I have written the macro below, however am coming up with an error that states there is no Do when trying to run it. Can anyone help? Range("7:7").Select 'Set range to start point Do While ActiveCell.Value < "" With Selection.Interior.ColorIndex = 35 ActiveRange.Offset(6, 0).Select Loop Thanks Kirsty |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop macro error
Thanks Mark,
I want every 6 rows, from row 7 on the sheet to be coloured 35, but only on the one specified worksheet "Mark Ivey" wrote: I might be able to help, but you need more to your code to prevent an endless loop. For starters... to get you out of error mode, here is the fixes for what you have posted. Range("7:7").Select 'Set range to start point Do While ActiveCell.Value < "" With Selection.Interior.ColorIndex = 35 End With ' to end the With loop ActiveRange.Offset(6, 0).Select Wend ' to end the While loop Loop ... but this creates an endless loop. You need something inside your WHILE loop to offset this endless state. What exactly are you trying to accomplish? Do you want all rows on each sheet to color 35? Or do you want just row 7 on each sheet to color 35? Please explain in further detail. Mark Ivey "Kirsty" wrote in message ... Hi, I am trying to write a macro that will loop through my spreadsheet until there is no more data. I want it to start at row 7, then colour the row (or just cells A to AD) to colour 35. I have written the macro below, however am coming up with an error that states there is no Do when trying to run it. Can anyone help? Range("7:7").Select 'Set range to start point Do While ActiveCell.Value < "" With Selection.Interior.ColorIndex = 35 ActiveRange.Offset(6, 0).Select Loop Thanks Kirsty |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop macro error
Kirsty,
See if this fits what you were wanting... Let me know if this is not it. Mark Sub every_six_rows() Dim LastRow As Long, i As Long, j As Long LastRow = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row For i = 7 To LastRow Step 6 For j = 1 To 30 If Cells(i, j).Value = "" Then Exit Sub Cells(i, j).Interior.ColorIndex = 35 Next Next End Sub "Kirsty" wrote in message ... Thanks Mark, I want every 6 rows, from row 7 on the sheet to be coloured 35, but only on the one specified worksheet "Mark Ivey" wrote: I might be able to help, but you need more to your code to prevent an endless loop. For starters... to get you out of error mode, here is the fixes for what you have posted. Range("7:7").Select 'Set range to start point Do While ActiveCell.Value < "" With Selection.Interior.ColorIndex = 35 End With ' to end the With loop ActiveRange.Offset(6, 0).Select Wend ' to end the While loop Loop ... but this creates an endless loop. You need something inside your WHILE loop to offset this endless state. What exactly are you trying to accomplish? Do you want all rows on each sheet to color 35? Or do you want just row 7 on each sheet to color 35? Please explain in further detail. Mark Ivey "Kirsty" wrote in message ... Hi, I am trying to write a macro that will loop through my spreadsheet until there is no more data. I want it to start at row 7, then colour the row (or just cells A to AD) to colour 35. I have written the macro below, however am coming up with an error that states there is no Do when trying to run it. Can anyone help? Range("7:7").Select 'Set range to start point Do While ActiveCell.Value < "" With Selection.Interior.ColorIndex = 35 ActiveRange.Offset(6, 0).Select Loop Thanks Kirsty |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop macro error
Thanks Mark,
Now it is working for the first four times, then stopping, even though I have about 300 rows of data. Kirsty "Mark Ivey" wrote: Kirsty, See if this fits what you were wanting... Let me know if this is not it. Mark Sub every_six_rows() Dim LastRow As Long, i As Long, j As Long LastRow = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row For i = 7 To LastRow Step 6 For j = 1 To 30 If Cells(i, j).Value = "" Then Exit Sub Cells(i, j).Interior.ColorIndex = 35 Next Next End Sub "Kirsty" wrote in message ... Thanks Mark, I want every 6 rows, from row 7 on the sheet to be coloured 35, but only on the one specified worksheet "Mark Ivey" wrote: I might be able to help, but you need more to your code to prevent an endless loop. For starters... to get you out of error mode, here is the fixes for what you have posted. Range("7:7").Select 'Set range to start point Do While ActiveCell.Value < "" With Selection.Interior.ColorIndex = 35 End With ' to end the With loop ActiveRange.Offset(6, 0).Select Wend ' to end the While loop Loop ... but this creates an endless loop. You need something inside your WHILE loop to offset this endless state. What exactly are you trying to accomplish? Do you want all rows on each sheet to color 35? Or do you want just row 7 on each sheet to color 35? Please explain in further detail. Mark Ivey "Kirsty" wrote in message ... Hi, I am trying to write a macro that will loop through my spreadsheet until there is no more data. I want it to start at row 7, then colour the row (or just cells A to AD) to colour 35. I have written the macro below, however am coming up with an error that states there is no Do when trying to run it. Can anyone help? Range("7:7").Select 'Set range to start point Do While ActiveCell.Value < "" With Selection.Interior.ColorIndex = 35 ActiveRange.Offset(6, 0).Select Loop Thanks Kirsty |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop macro error
You might want to try it without the empty cell exit function. See fix
below... Mark Sub every_six_rows() Dim LastRow As Long, i As Long, j As Long LastRow = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row For i = 7 To LastRow Step 6 For j = 1 To 30 Cells(i, j).Interior.ColorIndex = 35 Next Next End Sub "Kirsty" wrote in message ... Thanks Mark, Now it is working for the first four times, then stopping, even though I have about 300 rows of data. Kirsty "Mark Ivey" wrote: Kirsty, See if this fits what you were wanting... Let me know if this is not it. Mark Sub every_six_rows() Dim LastRow As Long, i As Long, j As Long LastRow = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row For i = 7 To LastRow Step 6 For j = 1 To 30 If Cells(i, j).Value = "" Then Exit Sub Cells(i, j).Interior.ColorIndex = 35 Next Next End Sub "Kirsty" wrote in message ... Thanks Mark, I want every 6 rows, from row 7 on the sheet to be coloured 35, but only on the one specified worksheet "Mark Ivey" wrote: I might be able to help, but you need more to your code to prevent an endless loop. For starters... to get you out of error mode, here is the fixes for what you have posted. Range("7:7").Select 'Set range to start point Do While ActiveCell.Value < "" With Selection.Interior.ColorIndex = 35 End With ' to end the With loop ActiveRange.Offset(6, 0).Select Wend ' to end the While loop Loop ... but this creates an endless loop. You need something inside your WHILE loop to offset this endless state. What exactly are you trying to accomplish? Do you want all rows on each sheet to color 35? Or do you want just row 7 on each sheet to color 35? Please explain in further detail. Mark Ivey "Kirsty" wrote in message ... Hi, I am trying to write a macro that will loop through my spreadsheet until there is no more data. I want it to start at row 7, then colour the row (or just cells A to AD) to colour 35. I have written the macro below, however am coming up with an error that states there is no Do when trying to run it. Can anyone help? Range("7:7").Select 'Set range to start point Do While ActiveCell.Value < "" With Selection.Interior.ColorIndex = 35 ActiveRange.Offset(6, 0).Select Loop Thanks Kirsty |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop macro error
Thanks Mark,
Works perfect "Mark Ivey" wrote: You might want to try it without the empty cell exit function. See fix below... Mark Sub every_six_rows() Dim LastRow As Long, i As Long, j As Long LastRow = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row For i = 7 To LastRow Step 6 For j = 1 To 30 Cells(i, j).Interior.ColorIndex = 35 Next Next End Sub "Kirsty" wrote in message ... Thanks Mark, Now it is working for the first four times, then stopping, even though I have about 300 rows of data. Kirsty "Mark Ivey" wrote: Kirsty, See if this fits what you were wanting... Let me know if this is not it. Mark Sub every_six_rows() Dim LastRow As Long, i As Long, j As Long LastRow = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row For i = 7 To LastRow Step 6 For j = 1 To 30 If Cells(i, j).Value = "" Then Exit Sub Cells(i, j).Interior.ColorIndex = 35 Next Next End Sub "Kirsty" wrote in message ... Thanks Mark, I want every 6 rows, from row 7 on the sheet to be coloured 35, but only on the one specified worksheet "Mark Ivey" wrote: I might be able to help, but you need more to your code to prevent an endless loop. For starters... to get you out of error mode, here is the fixes for what you have posted. Range("7:7").Select 'Set range to start point Do While ActiveCell.Value < "" With Selection.Interior.ColorIndex = 35 End With ' to end the With loop ActiveRange.Offset(6, 0).Select Wend ' to end the While loop Loop ... but this creates an endless loop. You need something inside your WHILE loop to offset this endless state. What exactly are you trying to accomplish? Do you want all rows on each sheet to color 35? Or do you want just row 7 on each sheet to color 35? Please explain in further detail. Mark Ivey "Kirsty" wrote in message ... Hi, I am trying to write a macro that will loop through my spreadsheet until there is no more data. I want it to start at row 7, then colour the row (or just cells A to AD) to colour 35. I have written the macro below, however am coming up with an error that states there is no Do when trying to run it. Can anyone help? Range("7:7").Select 'Set range to start point Do While ActiveCell.Value < "" With Selection.Interior.ColorIndex = 35 ActiveRange.Offset(6, 0).Select Loop Thanks Kirsty |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Do while loop error.... | Excel Programming | |||
Error message when using the Solver in a VBA macro loop | Excel Programming | |||
Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error | Excel Programming | |||
loop error | Excel Programming |