Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Problem
Hi,
I'm still trying to get to grips with VBA and would appreciate some help with this looping problem (I know its very basic normal users!): I'm trying to write a procedure that I can run on the active cell at the top of a list of names, so that it will fill the adjacent cell with a predifined colour: So far I've got to this: Sub SetAjacentCellColour() Dim rgPetName As Range Dim r As Integer Dim c As Integer Set rgPetName = ActiveCell r = ActiveCell.Row c = ActiveCell.Column Do If IsEmpty(Cells(r, c)) Then Exit Do Select Case rgPetName Case "Cat": Cells(r, c).Offset(0, 1).Interior.ColorIndex = 5 Case "Dog": Cells(r, c).Offset(0, 1).Interior.ColorIndex = 6 Case Else: MsgBox "Pet type not in colour list" End Select r = r + 1 'This is where its getting stuck rgPetName = Range(Cells(r, c)) Loop MsgBox "Finished" End Sub It's getting stuck on the part where I'm trying to tell it to move the rgPetName range down to the next cell. I know this isn't the right way so please feel free to set a new student straight. Many thanks John List example: Dog Cat Dog Dog Dog Cat |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Problem
On Fri, 9 Jul 2004 20:29:43 +0100, "John"
wrote: Hi, I'm still trying to get to grips with VBA and would appreciate some help with this looping problem (I know its very basic normal users!): I'm trying to write a procedure that I can run on the active cell at the top of a list of names, so that it will fill the adjacent cell with a predifined colour: So far I've got to this: Sub SetAjacentCellColour() Dim rgPetName As Range Dim r As Integer Dim c As Integer Set rgPetName = ActiveCell r = ActiveCell.Row c = ActiveCell.Column Do If IsEmpty(Cells(r, c)) Then Exit Do Select Case rgPetName Case "Cat": Cells(r, c).Offset(0, 1).Interior.ColorIndex = 5 Case "Dog": Cells(r, c).Offset(0, 1).Interior.ColorIndex = 6 Case Else: MsgBox "Pet type not in colour list" End Select r = r + 1 'This is where its getting stuck rgPetName = Range(Cells(r, c)) Loop MsgBox "Finished" End Sub It's getting stuck on the part where I'm trying to tell it to move the rgPetName range down to the next cell. I know this isn't the right way so please feel free to set a new student straight. Many thanks John List example: Dog Cat Dog Dog Dog Cat Try changing rgPetName = Range(Cells(r, c)) to rgPetName = Range(Cells(r, c),Cells(r,c)) Hope this helps Lars-Åke |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Problem
On Fri, 09 Jul 2004 19:48:59 GMT, Lars-Åke Aspelin
wrote: On Fri, 9 Jul 2004 20:29:43 +0100, "John" wrote: Hi, I'm still trying to get to grips with VBA and would appreciate some help with this looping problem (I know its very basic normal users!): I'm trying to write a procedure that I can run on the active cell at the top of a list of names, so that it will fill the adjacent cell with a predifined colour: So far I've got to this: Sub SetAjacentCellColour() Dim rgPetName As Range Dim r As Integer Dim c As Integer Set rgPetName = ActiveCell r = ActiveCell.Row c = ActiveCell.Column Do If IsEmpty(Cells(r, c)) Then Exit Do Select Case rgPetName Case "Cat": Cells(r, c).Offset(0, 1).Interior.ColorIndex = 5 Case "Dog": Cells(r, c).Offset(0, 1).Interior.ColorIndex = 6 Case Else: MsgBox "Pet type not in colour list" End Select r = r + 1 'This is where its getting stuck rgPetName = Range(Cells(r, c)) Loop MsgBox "Finished" End Sub It's getting stuck on the part where I'm trying to tell it to move the rgPetName range down to the next cell. I know this isn't the right way so please feel free to set a new student straight. Many thanks John List example: Dog Cat Dog Dog Dog Cat Try changing rgPetName = Range(Cells(r, c)) to rgPetName = Range(Cells(r, c),Cells(r,c)) Hope this helps Lars-Åke or simply rgPetName = Cells(r, c) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Problem
Thanks for your help Lars.
I've discovered that I was doing a couple of things incorrectly; I needed to change the "Set rgPetName = ActiveCell" to "Set rgPetName = Cells(r, c)" and to put this statement inside the loop. I was finding that the first item in the list was being renamed before that! Thanks again for you time. Best regards John "Lars-Åke Aspelin" wrote in message ... On Fri, 09 Jul 2004 19:48:59 GMT, Lars-Åke Aspelin wrote: On Fri, 9 Jul 2004 20:29:43 +0100, "John" wrote: Hi, I'm still trying to get to grips with VBA and would appreciate some help with this looping problem (I know its very basic normal users!): I'm trying to write a procedure that I can run on the active cell at the top of a list of names, so that it will fill the adjacent cell with a predifined colour: So far I've got to this: Sub SetAjacentCellColour() Dim rgPetName As Range Dim r As Integer Dim c As Integer Set rgPetName = ActiveCell r = ActiveCell.Row c = ActiveCell.Column Do If IsEmpty(Cells(r, c)) Then Exit Do Select Case rgPetName Case "Cat": Cells(r, c).Offset(0, 1).Interior.ColorIndex = 5 Case "Dog": Cells(r, c).Offset(0, 1).Interior.ColorIndex = 6 Case Else: MsgBox "Pet type not in colour list" End Select r = r + 1 'This is where its getting stuck rgPetName = Range(Cells(r, c)) Loop MsgBox "Finished" End Sub It's getting stuck on the part where I'm trying to tell it to move the rgPetName range down to the next cell. I know this isn't the right way so please feel free to set a new student straight. Many thanks John List example: Dog Cat Dog Dog Dog Cat Try changing rgPetName = Range(Cells(r, c)) to rgPetName = Range(Cells(r, c),Cells(r,c)) Hope this helps Lars-Åke or simply rgPetName = Cells(r, c) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
do while loop problem | Excel Discussion (Misc queries) | |||
loop problem | Excel Discussion (Misc queries) | |||
For Next loop problem | Excel Programming | |||
Loop Problem | Excel Programming | |||
For...Each Loop Problem | Excel Programming |