Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more loop question
Can someone please explain to me why the following code is skipping each row
in which the *first* instance of a new name appears in column A? In other words, the Case Statement starts evaluating on the 2nd occurrence of the name. (thanks Bob, for all your help so far...) TIA, Patti Option Explicit Private Sub testloop() Dim LstRow As Long Dim i As Long Dim AgtName As String Dim oCurrent As Range Dim FoundFlag As Boolean LstRow = Range("a" & Rows.Count).End(xlUp).Row AgtName = Range("A1").Value Set oCurrent = Range("A1") FoundFlag = False For i = 1 To LstRow If Cells(i, "A") = AgtName Then Do While FoundFlag = False Select Case Cells(i, 12) Case "" oCurrent.Offset(0, 15).Value = "I found a Null cell" FoundFlag = True Case "This" oCurrent.Offset(0, 15).Value = "I found This!" FoundFlag = True Case "That" oCurrent.Offset(0, 15).Value = "I found That!" FoundFlag = True Case Else FoundFlag = True End Select Loop ' message box added to confirm which row is being evaluated MsgBox "Currently evaluating row " & i & " for agent " & AgtName Else AgtName = Cells(i, "A").Value Set oCurrent = Cells(i, "A") FoundFlag = False End If Next i End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more loop question
Patti,
You skip a row on change of name rather than process it. I've tested it before the Do Loop, and removed the Else. Also, you don't need the oCurrent variable, you can use the index counter Public Sub testloop() Dim LstRow As Long Dim i As Long Dim AgtName As String Dim FoundFlag As Boolean LstRow = Range("A" & Rows.Count).End(xlUp).Row AgtName = Range("A1").Value FoundFlag = False For i = 1 To LstRow With Cells(i, "A") If .Value < AgtName Then AgtName = .Value FoundFlag = False End If Do While FoundFlag = False Select Case .Cells(1, 12).Value Case "" .Offset(0, 15).Value = "I found a Null cell" FoundFlag = True Case "This" .Offset(0, 15).Value = "I found This!" FoundFlag = True Case "That" .Offset(0, 15).Value = "I found That!" FoundFlag = True Case Else FoundFlag = True End Select Loop ' message box added to confirm which row is being evaluated MsgBox "Currently evaluating row " & i & " for agent " & AgtName End With Next i End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Patti" wrote in message ... Can someone please explain to me why the following code is skipping each row in which the *first* instance of a new name appears in column A? In other words, the Case Statement starts evaluating on the 2nd occurrence of the name. (thanks Bob, for all your help so far...) TIA, Patti Option Explicit Private Sub testloop() Dim LstRow As Long Dim i As Long Dim AgtName As String Dim oCurrent As Range Dim FoundFlag As Boolean LstRow = Range("a" & Rows.Count).End(xlUp).Row AgtName = Range("A1").Value Set oCurrent = Range("A1") FoundFlag = False For i = 1 To LstRow If Cells(i, "A") = AgtName Then Do While FoundFlag = False Select Case Cells(i, 12) Case "" oCurrent.Offset(0, 15).Value = "I found a Null cell" FoundFlag = True Case "This" oCurrent.Offset(0, 15).Value = "I found This!" FoundFlag = True Case "That" oCurrent.Offset(0, 15).Value = "I found That!" FoundFlag = True Case Else FoundFlag = True End Select Loop ' message box added to confirm which row is being evaluated MsgBox "Currently evaluating row " & i & " for agent " & AgtName Else AgtName = Cells(i, "A").Value Set oCurrent = Cells(i, "A") FoundFlag = False End If Next i End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more loop question
Hi Bob,
You are right that this now evaluates every row...but now it won't find the flag for each name unless it appears in the same row as the first instance of the name. So if I have in column A: Column L: Smith blah Smith This '( my flag) I want column P of the *first* row where Smith occurs to say "I found This!" The use of the oCurrent variable was actually a tip I got from you, to capture the row where I found the first instance of a new name. And of course it works great. (sorry if I broke any rules of netiquette by staring a new thread...nearly a week has passed since I last posted & I thought it unlikely that the last would be revisited). Anyway, I just don't understand the "why" behind it skipping a row on the change of name. As long as I'm stuck with this problem, I am trying to make it a good learning experience... I really appreciate all the help you given me. Patti "Bob Phillips" wrote in message ... Patti, You skip a row on change of name rather than process it. I've tested it before the Do Loop, and removed the Else. Also, you don't need the oCurrent variable, you can use the index counter Public Sub testloop() Dim LstRow As Long Dim i As Long Dim AgtName As String Dim FoundFlag As Boolean LstRow = Range("A" & Rows.Count).End(xlUp).Row AgtName = Range("A1").Value FoundFlag = False For i = 1 To LstRow With Cells(i, "A") If .Value < AgtName Then AgtName = .Value FoundFlag = False End If Do While FoundFlag = False Select Case .Cells(1, 12).Value Case "" .Offset(0, 15).Value = "I found a Null cell" FoundFlag = True Case "This" .Offset(0, 15).Value = "I found This!" FoundFlag = True Case "That" .Offset(0, 15).Value = "I found That!" FoundFlag = True Case Else FoundFlag = True End Select Loop ' message box added to confirm which row is being evaluated MsgBox "Currently evaluating row " & i & " for agent " & AgtName End With Next i End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Patti" wrote in message ... Can someone please explain to me why the following code is skipping each row in which the *first* instance of a new name appears in column A? In other words, the Case Statement starts evaluating on the 2nd occurrence of the name. (thanks Bob, for all your help so far...) TIA, Patti Option Explicit Private Sub testloop() Dim LstRow As Long Dim i As Long Dim AgtName As String Dim oCurrent As Range Dim FoundFlag As Boolean LstRow = Range("a" & Rows.Count).End(xlUp).Row AgtName = Range("A1").Value Set oCurrent = Range("A1") FoundFlag = False For i = 1 To LstRow If Cells(i, "A") = AgtName Then Do While FoundFlag = False Select Case Cells(i, 12) Case "" oCurrent.Offset(0, 15).Value = "I found a Null cell" FoundFlag = True Case "This" oCurrent.Offset(0, 15).Value = "I found This!" FoundFlag = True Case "That" oCurrent.Offset(0, 15).Value = "I found That!" FoundFlag = True Case Else FoundFlag = True End Select Loop ' message box added to confirm which row is being evaluated MsgBox "Currently evaluating row " & i & " for agent " & AgtName Else AgtName = Cells(i, "A").Value Set oCurrent = Cells(i, "A") FoundFlag = False End If Next i End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more loop question
"Patti" wrote in message
... Hi Bob, You are right that this now evaluates every row...but now it won't find the flag for each name unless it appears in the same row as the first instance of the name. So if I have in column A: Column L: Smith blah Smith This '( my flag) I thought I might have messed this bit up when I moved the Found flag, but I couldn't remember the exact requirment from your previous post. Explain what you want again. FRom this post I read it that you want to highlight the first row with another value in column L, but your case statement evaluates empty as a value. If the offset value is empty would this count as found, or should it just be This, That, etc. I want column P of the *first* row where Smith occurs to say "I found This!" In my testing, this happened, as all of mine had THis, That or were empty which i9s what the Case statement tells it to. The use of the oCurrent variable was actually a tip I got from you, to capture the row where I found the first instance of a new name. And of course it works great. Now stop that, throwing what I said back at me <VBG. If I recall, you would use that to save the cell and then use that cell object thereafcter, but the way you have implemented the code is to save all of the values separately (i as the row, AgtName as the agent name), so saving the cell as well is superfluous, not wrong, just overkill. (sorry if I broke any rules of netiquette by staring a new thread...nearly a week has passed since I last posted & I thought it unlikely that the last would be revisited). Who cares. I personally think there is a lot of fluff about netiquette. There are some practices that make life simpler, but they are not rules. Anyway, I just don't understand the "why" behind it skipping a row on the change of name. As long as I'm stuck with this problem, I am trying to make it a good learning experience... The why is because you had an If Else Endif. The If evaialuated the current row, whereas the Else reset Agtname. But each case looped to the next item, so the Else did not also go through the cell evaluation, whereas it does in my example because it is an indecent if, before the Case evaluation. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more loop question
Bob,
I'm sure this problem is nowhere near as confusing as my explanation my make it seem... For each name in column A, I need to look down a list for various codes in column L. Once I find a valid code, I want to quit looking, and enter what I found (via the case statement) in column P of the first row in which that name appeared. It has to be in the row with the _first_ instance of the name because once I get this Sub to work, I am going to delete rows so that each name appears only once (keeping the row where the name first appears). In my testing, this happened, as all of mine had THis, That or were empty which i9s what the Case statement tells it to. I probably should have mentioned that not all cells will have a valid code in them...I'm picking through the garbage that I don't care about. The reason for looking for the Null in the first Case statement is because I know that if I find a Null, the rest of the cells in column L for that particular name will be Null too... so I just want to quit looking, set my FoundFlag to True and move on to the next name. The why is because you had an If Else Endif. The If evaialuated the current row, whereas the Else reset Agtname. But each case looped to the next item, so the Else did not also go through the cell evaluation, whereas it does in my example because it is an indecent if, before the Case evaluation. Oh, I get it now... but how can I reset the Agtname _and_ have it evaluated in the first instance? I tried to use "offset" to set it to the name on the _next_ row, but it still skipped the first instance of a new name. If possible, I'd really like to stick close to the code I've got. I'm sure that there is always going to be a technically "better" method, but I really need to learn to do this myself, & it's hard to get a handle on when I keep "starting over". Thanks! Patti "Bob Phillips" wrote in message ... "Patti" wrote in message ... Hi Bob, You are right that this now evaluates every row...but now it won't find the flag for each name unless it appears in the same row as the first instance of the name. So if I have in column A: Column L: Smith blah Smith This '( my flag) I thought I might have messed this bit up when I moved the Found flag, but I couldn't remember the exact requirment from your previous post. Explain what you want again. FRom this post I read it that you want to highlight the first row with another value in column L, but your case statement evaluates empty as a value. If the offset value is empty would this count as found, or should it just be This, That, etc. I want column P of the *first* row where Smith occurs to say "I found This!" In my testing, this happened, as all of mine had THis, That or were empty which i9s what the Case statement tells it to. The use of the oCurrent variable was actually a tip I got from you, to capture the row where I found the first instance of a new name. And of course it works great. Now stop that, throwing what I said back at me <VBG. If I recall, you would use that to save the cell and then use that cell object thereafcter, but the way you have implemented the code is to save all of the values separately (i as the row, AgtName as the agent name), so saving the cell as well is superfluous, not wrong, just overkill. (sorry if I broke any rules of netiquette by staring a new thread...nearly a week has passed since I last posted & I thought it unlikely that the last would be revisited). Who cares. I personally think there is a lot of fluff about netiquette. There are some practices that make life simpler, but they are not rules. Anyway, I just don't understand the "why" behind it skipping a row on the change of name. As long as I'm stuck with this problem, I am trying to make it a good learning experience... The why is because you had an If Else Endif. The If evaialuated the current row, whereas the Else reset Agtname. But each case looped to the next item, so the Else did not also go through the cell evaluation, whereas it does in my example because it is an indecent if, before the Case evaluation. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more loop question
"Patti" wrote in message ... Bob, I'm sure this problem is nowhere near as confusing as my explanation my make it seem... For each name in column A, I need to look down a list for various codes in column L. Once I find a valid code, I want to quit looking, and enter what I found (via the case statement) in column P of the first row in which that name appeared. I am confused. It is the Case statement where it is found so the statement '... Once I find a valid code, I want to quit looking, and enter what I found (via the case statement) ...' is incompatible as I understand it. And, the CAse statement sets the Found flag to true whatever the value evaluated! It has to be in the row with the _first_ instance of the name because once I get this Sub to work, I am going to delete rows so that each name appears only once (keeping the row where the name first appears). But are we talking about the firts row with a valid code, or just the first row for the name? Oh, I get it now... but how can I reset the Agtname _and_ have it evaluated in the first instance? As I showed you in my follow-up response. I tried to use "offset" to set it to the name on the _next_ row, but it still skipped the first instance of a new name. That's because the If Else makes you skip it as I showed you. If possible, I'd really like to stick close to the code I've got. I'm sure that there is always going to be a technically "better" method, but I really need to learn to do this myself, & it's hard to get a handle on when I keep "starting over". But you can't, it doesn't work. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more loop question
But are we talking about the first row with a valid code, or just the
first row for the name? Always to the first row for the name... I'm moving the data I need to the first row for each name. In the end, duplicate rows will be deleted, so all info needs to be on the first row for that name. I am confused. It is the Case statement where it is found so the statement '... Once I find a valid code, I want to quit looking, and enter what I found (via the case statement) ...' is incompatible as I understand it. And, the CAse statement sets the Found flag to true whatever the value evaluated! I'm just not explaining myself well. Should be once I find a valid code OR a NULL. I real life, when I find a NULL, I just want to set FoundFlag to True so that it will END SELECT and go on to the next AgtName. In the test loop, I am writing a comment in column P when NULL is found purely so that I can see that it is working. That's because the If Else makes you skip it as I showed you. If possible, I'd really like to stick close to the code I've got. I'm sure that there is always going to be a technically "better" method, but I really need to learn to do this myself, & it's hard to get a handle on when I keep "starting over". But you can't, it doesn't work. That much I know ; ) I would have thought it'd be adaptable though. I know you provided alternate code that doesn't skip a row, but as I said, it only find a valid code OR Null ( my "FoundFlag") if appears in the same row as the first instance of the name. This won't always be the case... "Bob Phillips" wrote in message ... "Patti" wrote in message ... Bob, I'm sure this problem is nowhere near as confusing as my explanation my make it seem... For each name in column A, I need to look down a list for various codes in column L. Once I find a valid code, I want to quit looking, and enter what I found (via the case statement) in column P of the first row in which that name appeared. I am confused. It is the Case statement where it is found so the statement '... Once I find a valid code, I want to quit looking, and enter what I found (via the case statement) ...' is incompatible as I understand it. And, the CAse statement sets the Found flag to true whatever the value evaluated! It has to be in the row with the _first_ instance of the name because once I get this Sub to work, I am going to delete rows so that each name appears only once (keeping the row where the name first appears). But are we talking about the firts row with a valid code, or just the first row for the name? Oh, I get it now... but how can I reset the Agtname _and_ have it evaluated in the first instance? As I showed you in my follow-up response. I tried to use "offset" to set it to the name on the _next_ row, but it still skipped the first instance of a new name. That's because the If Else makes you skip it as I showed you. If possible, I'd really like to stick close to the code I've got. I'm sure that there is always going to be a technically "better" method, but I really need to learn to do this myself, & it's hard to get a handle on when I keep "starting over". But you can't, it doesn't work. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more loop question
"Patti" wrote in message
... But are we talking about the first row with a valid code, or just the first row for the name? Always to the first row for the name... I'm moving the data I need to the first row for each name. In the end, duplicate rows will be deleted, so all info needs to be on the first row for that name. I'm just not explaining myself well. Should be once I find a valid code OR a NULL. I real life, when I find a NULL, I just want to set FoundFlag to True so that it will END SELECT and go on to the next AgtName. In the test loop, I am writing a comment in column P when NULL is found purely so that I can see that it is working. We don't seem to be connecting. Those two statements above seem to be at odds to me. The first says pick up the first instance of a name, the second says pivck up the firtgs instance of a valid code. They may not be the same row. Map out some (good) exsample data, and expected results. That much I know ; ) I would have thought it'd be adaptable though. I know you provided alternate code that doesn't skip a row, but as I said, it only find a valid code OR Null ( my "FoundFlag") if appears in the same row as the first instance of the name. This won't always be the case... If we can get to the stage when I understand what the requirement is I will try and do that. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more loop question
I appreciate you hanging in there with me...
1) First, look for first instance of a name in column A 2) Within all rows for that name, look for first instance of either a Null OR a valid code in column L. To simplify, lets assume my only valid codes are "THIS" & "THAT". Again, if I find a Null, I don't need to look any further for that name. COLUMN A COLUMN L Jones Cat Jones THIS '< I need to capture this for Jones Smith Dog Smith Rat Smith Pig Smith THAT ' < I need to capture this for Smith Smith Rabbit Thomas '< I don't need to look any further for Thomas Thomas Thomas If I find a valid code for a particular name, I need to plunk a comment in Column P of the row where the first instance of that name appeared, then go on to the next name. The output would then look like COLUMN A COLUMN L COLUMN P Jones Cat "I found THIS" Jones THIS Smith Dog "I found THAT" Smith Rat Smith Pig Smith THAT Smith Rabbit Thomas Thomas Thomas I won't be able to check back until morning... You're awesome Bob! "Bob Phillips" wrote in message ... "Patti" wrote in message ... But are we talking about the first row with a valid code, or just the first row for the name? Always to the first row for the name... I'm moving the data I need to the first row for each name. In the end, duplicate rows will be deleted, so all info needs to be on the first row for that name. I'm just not explaining myself well. Should be once I find a valid code OR a NULL. I real life, when I find a NULL, I just want to set FoundFlag to True so that it will END SELECT and go on to the next AgtName. In the test loop, I am writing a comment in column P when NULL is found purely so that I can see that it is working. We don't seem to be connecting. Those two statements above seem to be at odds to me. The first says pick up the first instance of a name, the second says pivck up the firtgs instance of a valid code. They may not be the same row. Map out some (good) exsample data, and expected results. That much I know ; ) I would have thought it'd be adaptable though. I know you provided alternate code that doesn't skip a row, but as I said, it only find a valid code OR Null ( my "FoundFlag") if appears in the same row as the first instance of the name. This won't always be the case... If we can get to the stage when I understand what the requirement is I will try and do that. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more loop question
Darn, I hate when words wrap when you did not intend them to...please note
that column A only has names. Any other info just wrapped from what I put in the Columns for L & P. "Patti" wrote in message ... I appreciate you hanging in there with me... 1) First, look for first instance of a name in column A 2) Within all rows for that name, look for first instance of either a Null OR a valid code in column L. To simplify, lets assume my only valid codes are "THIS" & "THAT". Again, if I find a Null, I don't need to look any further for that name. COLUMN A COLUMN L Jones Cat Jones THIS '< I need to capture this for Jones Smith Dog Smith Rat Smith Pig Smith THAT ' < I need to capture this for Smith Smith Rabbit Thomas '< I don't need to look any further for Thomas Thomas Thomas If I find a valid code for a particular name, I need to plunk a comment in Column P of the row where the first instance of that name appeared, then go on to the next name. The output would then look like COLUMN A COLUMN L COLUMN P Jones Cat "I found THIS" Jones THIS Smith Dog "I found THAT" Smith Rat Smith Pig Smith THAT Smith Rabbit Thomas Thomas Thomas I won't be able to check back until morning... You're awesome Bob! "Bob Phillips" wrote in message ... "Patti" wrote in message ... But are we talking about the first row with a valid code, or just the first row for the name? Always to the first row for the name... I'm moving the data I need to the first row for each name. In the end, duplicate rows will be deleted, so all info needs to be on the first row for that name. I'm just not explaining myself well. Should be once I find a valid code OR a NULL. I real life, when I find a NULL, I just want to set FoundFlag to True so that it will END SELECT and go on to the next AgtName. In the test loop, I am writing a comment in column P when NULL is found purely so that I can see that it is working. We don't seem to be connecting. Those two statements above seem to be at odds to me. The first says pick up the first instance of a name, the second says pivck up the firtgs instance of a valid code. They may not be the same row. Map out some (good) exsample data, and expected results. That much I know ; ) I would have thought it'd be adaptable though. I know you provided alternate code that doesn't skip a row, but as I said, it only find a valid code OR Null ( my "FoundFlag") if appears in the same row as the first instance of the name. This won't always be the case... If we can get to the stage when I understand what the requirement is I will try and do that. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more loop question
Ok Patti, I understand now. The seem in g contradictions make sense now
trhat I see the data. Hopefully, this does it. I hade to get rid of the Do Loop. Private Sub testloop() Dim LstRow As Long Dim i As Long Dim AgtName As String Dim oCurrent As Range Dim FoundFlag As Boolean Columns("P:P").ClearContents LstRow = Range("a" & Rows.Count).End(xlUp).Row AgtName = Range("A1").Value Set oCurrent = Range("A1") FoundFlag = False For i = 1 To LstRow If Cells(i, "A") < AgtName Then AgtName = Cells(i, "A").Value Set oCurrent = Cells(i, "A") FoundFlag = False End If If FoundFlag = False And Cells(i, "A").Value = AgtName Then Select Case LCase(Cells(i, 12).Value) Case "" oCurrent.Offset(0, 15).Value = "I found a Null cell " FoundFlag = True Case "this" oCurrent.Offset(0, 15).Value = "I found This!" FoundFlag = True Case "that" oCurrent.Offset(0, 15).Value = "I found That!" FoundFlag = True Case Else End Select End If Next i End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Patti" wrote in message ... I appreciate you hanging in there with me... 1) First, look for first instance of a name in column A 2) Within all rows for that name, look for first instance of either a Null OR a valid code in column L. To simplify, lets assume my only valid codes are "THIS" & "THAT". Again, if I find a Null, I don't need to look any further for that name. COLUMN A COLUMN L Jones Cat Jones THIS '< I need to capture this for Jones Smith Dog Smith Rat Smith Pig Smith THAT ' < I need to capture this for Smith Smith Rabbit Thomas '< I don't need to look any further for Thomas Thomas Thomas If I find a valid code for a particular name, I need to plunk a comment in Column P of the row where the first instance of that name appeared, then go on to the next name. The output would then look like COLUMN A COLUMN L COLUMN P Jones Cat "I found THIS" Jones THIS Smith Dog "I found THAT" Smith Rat Smith Pig Smith THAT Smith Rabbit Thomas Thomas Thomas I won't be able to check back until morning... You're awesome Bob! "Bob Phillips" wrote in message ... "Patti" wrote in message ... But are we talking about the first row with a valid code, or just the first row for the name? Always to the first row for the name... I'm moving the data I need to the first row for each name. In the end, duplicate rows will be deleted, so all info needs to be on the first row for that name. I'm just not explaining myself well. Should be once I find a valid code OR a NULL. I real life, when I find a NULL, I just want to set FoundFlag to True so that it will END SELECT and go on to the next AgtName. In the test loop, I am writing a comment in column P when NULL is found purely so that I can see that it is working. We don't seem to be connecting. Those two statements above seem to be at odds to me. The first says pick up the first instance of a name, the second says pivck up the firtgs instance of a valid code. They may not be the same row. Map out some (good) exsample data, and expected results. That much I know ; ) I would have thought it'd be adaptable though. I know you provided alternate code that doesn't skip a row, but as I said, it only find a valid code OR Null ( my "FoundFlag") if appears in the same row as the first instance of the name. This won't always be the case... If we can get to the stage when I understand what the requirement is I will try and do that. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more loop question
Bob, that is a beautiful thing! You're a gem - thanks again for all your
help. Patti "Bob Phillips" wrote in message ... Ok Patti, I understand now. The seem in g contradictions make sense now trhat I see the data. Hopefully, this does it. I hade to get rid of the Do Loop. Private Sub testloop() Dim LstRow As Long Dim i As Long Dim AgtName As String Dim oCurrent As Range Dim FoundFlag As Boolean Columns("P:P").ClearContents LstRow = Range("a" & Rows.Count).End(xlUp).Row AgtName = Range("A1").Value Set oCurrent = Range("A1") FoundFlag = False For i = 1 To LstRow If Cells(i, "A") < AgtName Then AgtName = Cells(i, "A").Value Set oCurrent = Cells(i, "A") FoundFlag = False End If If FoundFlag = False And Cells(i, "A").Value = AgtName Then Select Case LCase(Cells(i, 12).Value) Case "" oCurrent.Offset(0, 15).Value = "I found a Null cell " FoundFlag = True Case "this" oCurrent.Offset(0, 15).Value = "I found This!" FoundFlag = True Case "that" oCurrent.Offset(0, 15).Value = "I found That!" FoundFlag = True Case Else End Select End If Next i End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Patti" wrote in message ... I appreciate you hanging in there with me... 1) First, look for first instance of a name in column A 2) Within all rows for that name, look for first instance of either a Null OR a valid code in column L. To simplify, lets assume my only valid codes are "THIS" & "THAT". Again, if I find a Null, I don't need to look any further for that name. COLUMN A COLUMN L Jones Cat Jones THIS '< I need to capture this for Jones Smith Dog Smith Rat Smith Pig Smith THAT ' < I need to capture this for Smith Smith Rabbit Thomas '< I don't need to look any further for Thomas Thomas Thomas If I find a valid code for a particular name, I need to plunk a comment in Column P of the row where the first instance of that name appeared, then go on to the next name. The output would then look like COLUMN A COLUMN L COLUMN P Jones Cat "I found THIS" Jones THIS Smith Dog "I found THAT" Smith Rat Smith Pig Smith THAT Smith Rabbit Thomas Thomas Thomas I won't be able to check back until morning... You're awesome Bob! "Bob Phillips" wrote in message ... "Patti" wrote in message ... But are we talking about the first row with a valid code, or just the first row for the name? Always to the first row for the name... I'm moving the data I need to the first row for each name. In the end, duplicate rows will be deleted, so all info needs to be on the first row for that name. I'm just not explaining myself well. Should be once I find a valid code OR a NULL. I real life, when I find a NULL, I just want to set FoundFlag to True so that it will END SELECT and go on to the next AgtName. In the test loop, I am writing a comment in column P when NULL is found purely so that I can see that it is working. We don't seem to be connecting. Those two statements above seem to be at odds to me. The first says pick up the first instance of a name, the second says pivck up the firtgs instance of a valid code. They may not be the same row. Map out some (good) exsample data, and expected results. That much I know ; ) I would have thought it'd be adaptable though. I know you provided alternate code that doesn't skip a row, but as I said, it only find a valid code OR Null ( my "FoundFlag") if appears in the same row as the first instance of the name. This won't always be the case... If we can get to the stage when I understand what the requirement is I will try and do that. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop question | Excel Discussion (Misc queries) | |||
Loop question | Excel Discussion (Misc queries) | |||
Password Loop question. | Excel Discussion (Misc queries) | |||
another loop question | Excel Programming | |||
For loop question | Excel Programming |