another autofilter question
 

is there a way to compare a cells value to an adjacent cell, above the current
cell, so i can compare the 2? can't use offset and
..SpecialCells(xlCellTypeVisible) isn't cooperating with me.

just don't know how to get the cell value of the cell above the current cell.

thanks

--


Gary

another autofilter question
 

Take a look at Debra's site, she is an expert with filtering data

http://www.contextures.com/index.html

HTH

Raymond


Gary Keramidas wrote:
is there a way to compare a cells value to an adjacent cell, above the current
cell, so i can compare the 2? can't use offset and
.SpecialCells(xlCellTypeVisible) isn't cooperating with me.

just don't know how to get the cell value of the cell above the current cell.

thanks

--


Gary

another autofilter question
 

i looked there before and i didn't see what i wanted

--


Gary


wrote in message
ps.com...

Take a look at Debra's site, she is an expert with filtering data

http://www.contextures.com/index.html

HTH

Raymond


Gary Keramidas wrote:
is there a way to compare a cells value to an adjacent cell, above the
current
cell, so i can compare the 2? can't use offset and
.SpecialCells(xlCellTypeVisible) isn't cooperating with me.

just don't know how to get the cell value of the cell above the current cell.

thanks

--


Gary

another autofilter question
 

One way is to just look above, but check to see if that cell above is visible.
If it's not, just keep looking.

Another way is to look at each area in the visible cells (in that column).

dim rngV as range
dim myArea as range
dim myCell as range
dim VCellAbove as range
dim FoundIt as boolean
dim aCtr as long

with activesheet.autofilter.range
set rngv = .columns(1).resize(.rows.count-1,1) _
.offset(1,0).cells.specialcells(xlcelltypevisible)
end with

set mycell = .range("a99") 'who knows what cell you're looking for

foundit = false
for aCtr = 1 to rngv.areas.count
if intersect(rngv.areas(ictr), mycell) then
foundit = true
exit for
end if
next actr

if foundit = false then
msgbox "not in the correct range"
exit sub
end if

set vcellabove = nothing
if mycell.address = rngv.areas(actr).cells(1).address then
if actr = 1 then
'nothing above this cell in the top area
else
'last cell in previous area
with rngv.areas(actr-1)
set vcellabove = .cells(.cells.count)
end with
end if
else
'just the cell above
set vcellabove = mycell.offset(0,-1)
end if

if vcellabove is nothing then
msgbox "Nothing above"
else
msgbox vcellabove.address
end if

=========
Watch for typos--it's untested and uncompiled.

Gary Keramidas wrote:

is there a way to compare a cells value to an adjacent cell, above the current
cell, so i can compare the 2? can't use offset and
.SpecialCells(xlCellTypeVisible) isn't cooperating with me.

just don't know how to get the cell value of the cell above the current cell.

thanks

--

Gary

--

Dave Peterson

another autofilter question
 

thanks for the idea, dave. here's the code i used.
i look at the first visible cell in column I.
then i add 1 to the offset until the visible range count =2
then i store and compare the 2 values
if they're the same, i set the offset back to one and use that cell as the base
address and loop again.
once it finds 2 that aren't equal, it draws a border from column B to X to
separate the values.

not sure how elegant it is, but it works.
example:
G
----
B
B
B
-----
D
-----
G

lastrow = Worksheets("Production").Cells(Rows.Count, "A").End(xlUp).Row
frow = ws.Range("A1").End(xlDown).Row
ws.Range("I" & frow).Select
Set rng = ws.Range("I" & frow & ":I" & lastrow).SpecialCells(xlCellTypeVisible)
i = 1
For Each cell In rng
If Range(cell, cell.Offset(i, 0)).SpecialCells(xlCellTypeVisible).Count
_
= 1 Then
Do
i = i + 1
Loop Until Range(cell, cell.Offset(i, _
0)).SpecialCells(xlCellTypeVisible).Count = 2
End If

t1 = cell.Value
t2 = cell.Offset(i, 0)
If t1 < t2 Then
With cell.Offset(i, -7).Resize(1, 23).Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 5 '3 ,5, 50
End With
End If
i = 1
Next


--


Gary


"Dave Peterson" wrote in message
...
One way is to just look above, but check to see if that cell above is visible.
If it's not, just keep looking.

Another way is to look at each area in the visible cells (in that column).

dim rngV as range
dim myArea as range
dim myCell as range
dim VCellAbove as range
dim FoundIt as boolean
dim aCtr as long

with activesheet.autofilter.range
set rngv = .columns(1).resize(.rows.count-1,1) _
.offset(1,0).cells.specialcells(xlcelltypevisible)
end with

set mycell = .range("a99") 'who knows what cell you're looking for

foundit = false
for aCtr = 1 to rngv.areas.count
if intersect(rngv.areas(ictr), mycell) then
foundit = true
exit for
end if
next actr

if foundit = false then
msgbox "not in the correct range"
exit sub
end if

set vcellabove = nothing
if mycell.address = rngv.areas(actr).cells(1).address then
if actr = 1 then
'nothing above this cell in the top area
else
'last cell in previous area
with rngv.areas(actr-1)
set vcellabove = .cells(.cells.count)
end with
end if
else
'just the cell above
set vcellabove = mycell.offset(0,-1)
end if

if vcellabove is nothing then
msgbox "Nothing above"
else
msgbox vcellabove.address
end if

=========
Watch for typos--it's untested and uncompiled.

Gary Keramidas wrote:

is there a way to compare a cells value to an adjacent cell, above the
current
cell, so i can compare the 2? can't use offset and
.SpecialCells(xlCellTypeVisible) isn't cooperating with me.

just don't know how to get the cell value of the cell above the current cell.

thanks

--

Gary

--

Dave Peterson

another autofilter question
 

dave, i did have it working with another approach before that. here is the code
i used. they both seem to work fine. let me know if you see any issues.

thanks

frow = ws.Range("A1").End(xlDown).Row
ws.Range("I" & frow).Select
Set rng = ws.Range(Cells(ActiveCell.Row + 1, ActiveCell.Column),
Cells(Rows.Count, ActiveCell.Column))

Do
t1 = ActiveCell.Value
rng.SpecialCells(xlCellTypeVisible).Cells(1).Selec t
t2 = rng.SpecialCells(xlCellTypeVisible).Cells(1).Value

If t1 < t2 Then

With ActiveCell.Offset(0, -7).Resize(1, 23).Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 5
End With

Else

Set rng = ws.Range(Cells(ActiveCell.Row + 1, ActiveCell.Column),
Cells(Rows.Count, ActiveCell.Column))
End If

Loop While ActiveCell.Row < lastrow

--


Gary


"Dave Peterson" wrote in message
...
One way is to just look above, but check to see if that cell above is visible.
If it's not, just keep looking.

Another way is to look at each area in the visible cells (in that column).

dim rngV as range
dim myArea as range
dim myCell as range
dim VCellAbove as range
dim FoundIt as boolean
dim aCtr as long

with activesheet.autofilter.range
set rngv = .columns(1).resize(.rows.count-1,1) _
.offset(1,0).cells.specialcells(xlcelltypevisible)
end with

set mycell = .range("a99") 'who knows what cell you're looking for

foundit = false
for aCtr = 1 to rngv.areas.count
if intersect(rngv.areas(ictr), mycell) then
foundit = true
exit for
end if
next actr

if foundit = false then
msgbox "not in the correct range"
exit sub
end if

set vcellabove = nothing
if mycell.address = rngv.areas(actr).cells(1).address then
if actr = 1 then
'nothing above this cell in the top area
else
'last cell in previous area
with rngv.areas(actr-1)
set vcellabove = .cells(.cells.count)
end with
end if
else
'just the cell above
set vcellabove = mycell.offset(0,-1)
end if

if vcellabove is nothing then
msgbox "Nothing above"
else
msgbox vcellabove.address
end if

=========
Watch for typos--it's untested and uncompiled.

Gary Keramidas wrote:

is there a way to compare a cells value to an adjacent cell, above the
current
cell, so i can compare the 2? can't use offset and
.SpecialCells(xlCellTypeVisible) isn't cooperating with me.

just don't know how to get the cell value of the cell above the current cell.

thanks

--

Gary

--

Dave Peterson