i have filtered data and trying to do the calculation with the one row above or one row down of the filtered data .how to achieve that . please advise?
Code:
Sub GarsButSuspense()
c = 64
rc = Cells(Rows.Count, "A").End(xlUp).Row
With Range("A1", Cells(rc, c))
.AutoFilter Field:=64, Criteria1:="Filter", Operator:=xlFilterValues
End With
Dim r As Range
Dim CalnWithUpRow As Long
Dim CalnWithDownRow As Long
For Each r In Range("A2:A800000").SpecialCells(xlCellTypeVisible)
If Left(r.Offset(, 3).Value, 1) = 9 Then
'Not able to get the offset valu[B]e for the below code[/B]
CalnWithUpRow = r.Offset(, 11).Value + r.Offset([B]-1[/B], 11).Value 'Offset code is returning the value of hidden row cell value
CalnWithDownRow = r.Offset(, 11).Value + r.Offset([B]1[/B], 11).Value ''Offset code is returning the value of hidden row cell value
If CalnWithUpRow = 0 Then
r.Offset(, 59).Value = "GARS Break- to be posted but Suspense"
r.Offset(-1, 59).Value = "GARS Break- to be posted but Suspense"
ElseIf CalnWithDownRow = 0 Then
r.Offset(, 59).Value = "GARS Break- to be posted but Suspense"
r.Offset(1, 59).Value = "GARS Break- to be posted but Suspense"
End If
End If
Next r
End Sub
