Tom Ogilvy or somebody else, please help

[gaba]

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

 

[Tom Ogilvy]

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.

 

[gaba]

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

 

[gaba]

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

 

[gaba]

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

 

[Tom Ogilvy]

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

 

[gaba]

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