Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tom Ogilvy or somebody else, please help
Hi Tom, I'm sorry to bother you but If you can at least give me an idea on
how to start or how to approach the solution I would greatly appreciate it. I've post a question few hours ago: Find two values and select data in between. I don't want to duplicate the question but I'm stuck and I need this to be done today... I'm going in circles Thank you so much -- gaba :) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tom Ogilvy or somebody else, please help
Your problem statement said you had good or bad in column G, but you want to
copy good rows to G14 and below. At the same time, you actually said Cut, so if one was looping, one would need to also manage the good/bad indicators in column G and in that case, G14 would be a moving target. Also, if the original data extends beyond 13 rows, the good rows would overwrite the good/bad indicators. Also, you say between A:F, does that mean copy B:E or is it inclusive (A:F, with A and F included). Also, your description about between two values is unclear. It could be interpreted that one wants to find the first two Good values in column G and copy the rows between to G14 - however you say A:F, (column designations), so it really isn't very clear what you want to do. If your are just trying to separate the good and the bad into two separate groups, then Personally, I would copy all the data to another location, then delete all the bad at the new location and delete all the good at the original location. Perhaps you can give a clearer explanation of what you are trying to do. -- Regards, Tom Ogilvy "gaba" wrote in message ... Hi Tom, I'm sorry to bother you but If you can at least give me an idea on how to start or how to approach the solution I would greatly appreciate it. I've post a question few hours ago: Find two values and select data in between. I don't want to duplicate the question but I'm stuck and I need this to be done today... I'm going in circles Thank you so much -- gaba :) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tom Ogilvy or somebody else, please help
Hi Tom, so glad to know you are there...
I'm sorry I wasn't clear. This is the data I'm trying to select A B C D E F G -8 DL 0.0043 106% 22 Bad -9 STD 2 0.1038 130% 23 -10 STD 2 0.0995 124% 24 -13 DL 0.0044 109% 27 Good -14 STD 2 0.0865 108% 28 -16 STD1 0.0009 108% 29 -18 STD3 0.003 106% 30 -23 DL 0.0041 101% 37 Good And so on Flags are based on the DL percent value. This makes the rest of the data good or bad to the next flag. You are right about the cut and paste, I should do that on the next step, separated from selecting (coloring) bad data. I'll try to find the first two flags and if they are "good" and next "bad" select A to F in between the flags and set interior color = 40. Same if the flags are "bad" and "good". If flags are "good" and "good" I'll fill that section of column G with some flag value and in the next step cut and paste the selection. It doesn't matter that later on I'll overwrite the flags. I just need them for this step. I'm lost on how to keep going thru column G and keep track of the flags since I need to check them by pairs. The last flag will become first in the next pair. Hope this is not too confusing. Am I approaching this in the right way or there is another way (simpler) to do it? Thanks again, Gaba "Tom Ogilvy" wrote: Your problem statement said you had good or bad in column G, but you want to copy good rows to G14 and below. At the same time, you actually said Cut, so if one was looping, one would need to also manage the good/bad indicators in column G and in that case, G14 would be a moving target. Also, if the original data extends beyond 13 rows, the good rows would overwrite the good/bad indicators. Also, you say between A:F, does that mean copy B:E or is it inclusive (A:F, with A and F included). Also, your description about between two values is unclear. It could be interpreted that one wants to find the first two Good values in column G and copy the rows between to G14 - however you say A:F, (column designations), so it really isn't very clear what you want to do. If your are just trying to separate the good and the bad into two separate groups, then Personally, I would copy all the data to another location, then delete all the bad at the new location and delete all the good at the original location. Perhaps you can give a clearer explanation of what you are trying to do. -- Regards, Tom Ogilvy "gaba" wrote in message ... Hi Tom, I'm sorry to bother you but If you can at least give me an idea on how to start or how to approach the solution I would greatly appreciate it. I've post a question few hours ago: Find two values and select data in between. I don't want to duplicate the question but I'm stuck and I need this to be done today... I'm going in circles Thank you so much -- gaba :) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tom Ogilvy or somebody else, please help
Please don't laugh and forgive my ignorance. What can I do to get the second
c.address (gnextadd) and work with the data in between? I've declared d and e as Variants but that's not doing anything. How can I set gnextadd to be the next c (ActiveCell)? Range("G14").Select On Error Resume Next For Each c In Range("G14", "G" & lastRow) If c.Value = "" Then ActiveCell.Offset(1, 0).Select ElseIf c.Value < "" Then g1add = c.Address g1 = c.Value For Each d In Range(g1add + 1, "G" & lastRow) If d = "" Then ActiveCell.Offset(1, 0).Select ElseIf d < "" Then gnextadd = d.Address gnext = d Range(g1add, gnextadd).Select If g1 = "Good" And gnext = "Good" Then For Each e In Range(g1add + 1, gnextadd - 1) e.Value = "Ok" Next e ElseIf g1 = "Good" And gnext = "Bad" Then For Each e In Range(g1add + 1, gnextadd - 1) e.Value = "B" Next e ElseIf g1 = "Bad" And gnext = "Bad" Then For Each e In Range(g1add + 1, gnextadd - 1) e.Value = "B" Next e End If End If Next 'c.Address = gnext End If Err.Clear Next -- Thanks gaba :) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tom Ogilvy or somebody else, please help
Thanks Tom for your guidence. After reading your post I've realized I was
trying to do too much in one step. It took make few hours but it is working. As usual you saved the day. Gaba |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tom Ogilvy or somebody else, please help
Dim lastRow as Long, rng as Range
dim cell as Range, cell1 as Range lastRow = cells(rows.count,"G").End(xlup) set rng = Range(Cells(1,"G"),Cells(lastrow,"G")) for each cell in rng if cell.Row = lastrow then exit for set cell1 = cell.end(xldown) if cell1 = "Good" and cell = "Good" then ' do what else set rng1 = range(cell.offset(1,0),cell.offset(-1,0)) rng1.offset(0,-6).Resize(,6).ColorIndex = 40 end if Next -- Regards, Tom Ogilvy "gaba" wrote in message ... Hi Tom, so glad to know you are there... I'm sorry I wasn't clear. This is the data I'm trying to select A B C D E F G -8 DL 0.0043 106% 22 Bad -9 STD 2 0.1038 130% 23 -10 STD 2 0.0995 124% 24 -13 DL 0.0044 109% 27 Good -14 STD 2 0.0865 108% 28 -16 STD1 0.0009 108% 29 -18 STD3 0.003 106% 30 -23 DL 0.0041 101% 37 Good And so on Flags are based on the DL percent value. This makes the rest of the data good or bad to the next flag. You are right about the cut and paste, I should do that on the next step, separated from selecting (coloring) bad data. I'll try to find the first two flags and if they are "good" and next "bad" select A to F in between the flags and set interior color = 40. Same if the flags are "bad" and "good". If flags are "good" and "good" I'll fill that section of column G with some flag value and in the next step cut and paste the selection. It doesn't matter that later on I'll overwrite the flags. I just need them for this step. I'm lost on how to keep going thru column G and keep track of the flags since I need to check them by pairs. The last flag will become first in the next pair. Hope this is not too confusing. Am I approaching this in the right way or there is another way (simpler) to do it? Thanks again, Gaba "Tom Ogilvy" wrote: Your problem statement said you had good or bad in column G, but you want to copy good rows to G14 and below. At the same time, you actually said Cut, so if one was looping, one would need to also manage the good/bad indicators in column G and in that case, G14 would be a moving target. Also, if the original data extends beyond 13 rows, the good rows would overwrite the good/bad indicators. Also, you say between A:F, does that mean copy B:E or is it inclusive (A:F, with A and F included). Also, your description about between two values is unclear. It could be interpreted that one wants to find the first two Good values in column G and copy the rows between to G14 - however you say A:F, (column designations), so it really isn't very clear what you want to do. If your are just trying to separate the good and the bad into two separate groups, then Personally, I would copy all the data to another location, then delete all the bad at the new location and delete all the good at the original location. Perhaps you can give a clearer explanation of what you are trying to do. -- Regards, Tom Ogilvy "gaba" wrote in message ... Hi Tom, I'm sorry to bother you but If you can at least give me an idea on how to start or how to approach the solution I would greatly appreciate it. I've post a question few hours ago: Find two values and select data in between. I don't want to duplicate the question but I'm stuck and I need this to be done today... I'm going in circles Thank you so much -- gaba :) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tom Ogilvy or somebody else, please help
Tom: Thanks so much again and again. It works beautifuly.
I need to improve my logic and learn not to panic... I start messing with the code and trying crazy things. Good night, Gaba :) "Tom Ogilvy" wrote: Dim lastRow as Long, rng as Range dim cell as Range, cell1 as Range lastRow = cells(rows.count,"G").End(xlup) set rng = Range(Cells(1,"G"),Cells(lastrow,"G")) for each cell in rng if cell.Row = lastrow then exit for set cell1 = cell.end(xldown) if cell1 = "Good" and cell = "Good" then ' do what else set rng1 = range(cell.offset(1,0),cell.offset(-1,0)) rng1.offset(0,-6).Resize(,6).ColorIndex = 40 end if Next -- Regards, Tom Ogilvy "gaba" wrote in message ... Hi Tom, so glad to know you are there... I'm sorry I wasn't clear. This is the data I'm trying to select A B C D E F G -8 DL 0.0043 106% 22 Bad -9 STD 2 0.1038 130% 23 -10 STD 2 0.0995 124% 24 -13 DL 0.0044 109% 27 Good -14 STD 2 0.0865 108% 28 -16 STD1 0.0009 108% 29 -18 STD3 0.003 106% 30 -23 DL 0.0041 101% 37 Good And so on Flags are based on the DL percent value. This makes the rest of the data good or bad to the next flag. You are right about the cut and paste, I should do that on the next step, separated from selecting (coloring) bad data. I'll try to find the first two flags and if they are "good" and next "bad" select A to F in between the flags and set interior color = 40. Same if the flags are "bad" and "good". If flags are "good" and "good" I'll fill that section of column G with some flag value and in the next step cut and paste the selection. It doesn't matter that later on I'll overwrite the flags. I just need them for this step. I'm lost on how to keep going thru column G and keep track of the flags since I need to check them by pairs. The last flag will become first in the next pair. Hope this is not too confusing. Am I approaching this in the right way or there is another way (simpler) to do it? Thanks again, Gaba "Tom Ogilvy" wrote: Your problem statement said you had good or bad in column G, but you want to copy good rows to G14 and below. At the same time, you actually said Cut, so if one was looping, one would need to also manage the good/bad indicators in column G and in that case, G14 would be a moving target. Also, if the original data extends beyond 13 rows, the good rows would overwrite the good/bad indicators. Also, you say between A:F, does that mean copy B:E or is it inclusive (A:F, with A and F included). Also, your description about between two values is unclear. It could be interpreted that one wants to find the first two Good values in column G and copy the rows between to G14 - however you say A:F, (column designations), so it really isn't very clear what you want to do. If your are just trying to separate the good and the bad into two separate groups, then Personally, I would copy all the data to another location, then delete all the bad at the new location and delete all the good at the original location. Perhaps you can give a clearer explanation of what you are trying to do. -- Regards, Tom Ogilvy "gaba" wrote in message ... Hi Tom, I'm sorry to bother you but If you can at least give me an idea on how to start or how to approach the solution I would greatly appreciate it. I've post a question few hours ago: Find two values and select data in between. I don't want to duplicate the question but I'm stuck and I need this to be done today... I'm going in circles Thank you so much -- gaba :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Thank You Tom Ogilvy | Excel Worksheet Functions | |||
Thank u Tom Ogilvy | Excel Programming | |||
Tom Ogilvy | Excel Programming | |||
Tom Ogilvy | Excel Programming | |||
Help Tom Ogilvy | Excel Programming |