Solved: AND not working in loop

[anandbohra]

Hi Friends,
i am Back with some doubt.

I am making a For loop which runs on each cell in given range with condition in if statement.
let me make u clear
I want to highlight cells where
current cell value is > +2 or <-2 (this will be provided by user )
current cell + 3 offset value >+3 or <-3 (again user give 2 or 3 or whatever)

then highlight current cell so my data will be more acquarate to observe.

pl find the attached & rever back for further clarification.
file contains 3 sheets.
example sheets makes u clear about my output
condition sheets is for user to input condition i.e. as told 2 or 3
last sheet is actual data which i want to highlight with loop as made by me.

the only drawback in my loop is it proceed with value before and statmenet & not the full one.

pl help me.

[Bob Phillips]

Sub st1_filter()
Dim PriceRay As Range
Dim xVal As Variant
Dim myColl As New Collection
Dim ST1Priceband, ST1OIband, filtertype, I

ST1Priceband = Sheet3.Range("C2").Value 'value 2
ST1OIband = Sheet3.Range("D2").Value 'value 2
filtertype = Sheet3.Range("E2").Value
'If filtertype = "and" Then MsgBox "OK"
' The above mentioned will come for next step i.e. "and" or "or" filter


With ThisWorkbook.Worksheets("actual data")
Set PriceRay = .Range(.Range("E2"), .Range("E65536").End(xlUp))
End With
On Error Resume Next
For Each xVal In PriceRay

If Abs(xVal.Offset(, 3)) >= ST1OIband And _
Abs(xVal.Value) >= ST1Priceband Then
xVal.Interior.ColorIndex = 27
xVal.Offset(, 3).Interior.ColorIndex = 27
End If
Next xVal
On Error GoTo 0

End Sub

[anandbohra]

Thanks XLD I got the same posting my code
but now I am stuck to delete the same instead of highlighting
earlier i was highlighting the matched entires now I want to delete the unmatched entires.

Sub st1_filter()
Dim PriceRay As Range
Dim xVal As Variant
Dim myColl As New Collection
Dim ST1Priceband, ST1OIband, filtertype, I

ST1Priceband = Sheet3.Range("C2").Value 'value 2
ST1OIband = Sheet3.Range("D2").Value 'value 2
filtertype = Sheet3.Range("E2").Value
'If filtertype = "and" Then MsgBox "OK"
' The above mentioned will come for next step i.e. "and" or "or" filter


With ThisWorkbook.Worksheets("actual data")
Set PriceRay = Range(.Range("E2"), .Range("E65536").End(xlUp))
End With
On Error Resume Next
For Each xVal In PriceRay
If Not ((xVal.Value >= ST1Priceband Or xVal.Value <= ST1Priceband * -1) And (xVal.Offset(, 3) >= ST1OIband Or xVal.Offset(, 3) <= ST1OIband * -1)) Then
xVal.EntireRow.Delete
End If

On Error GoTo 0

End Sub



problem is I have to run this code 3-5 times to get exact output
coz i am deleting from above
u had earlier told me or someone that delete should begin from end

so will u pl amend the code to delete from the end

pl awaiting for reply

[Bob Phillips]

Why have you reverted to the code that didn't work instead of the code I gave you?

[anandbohra]

My earlier code was not working (in attachment)
but after few brackets I am able to crack the same & that was working (my post)
now when i got acceptable solution (no doubt your one was far better than mine as u use abs function) i put the same with NOT function

like if not ((.... or ....) and (... or ...)) then
now instead of highlighing I want to delete that records
so I will left with genuine data only

now i am stuck to delete code coz I am deleting from top but I want the code which delete from bottom (row by row)

[Bob Phillips]

Dim rng As Range
For Each xval In PriceRay

If Abs(xval.Offset(, 3)) < ST1OIband Or _
Abs(xval.Value) < ST1Priceband Then

If rng Is Nothing Then

Set rng = xval
Else

Set rng = Union(rng, xval)
End If
End If
Next xval

If Not rng Is Nothing Then rng.EntireRow.Delete

[anandbohra]

Fantastic Code :clap: :clap: :clap: :clap: :clap:

Purpose Solved :beerchug:


Thank you very much for your support
I am using your code only fully redrafting here who wants quick glimpse of final code

Option Explicit
Sub st1_filter()
Dim PriceRay As Range
Dim xVal As Variant
Dim ST1Priceband, ST1OIband, filtertype
Dim rng As Range

ST1Priceband = Sheet3.Range("C2").Value 'value 2
ST1OIband = Sheet3.Range("D2").Value 'value 2
filtertype = Sheet3.Range("E2").Value


With ThisWorkbook.Worksheets("actual data")
Set PriceRay = Range(.Range("E2"), .Range("E65536").End(xlUp))
End With
On Error Resume Next
For Each xVal In PriceRay
If Not (Abs(xVal.Value) >= ST1Priceband And Abs(xVal.Offset(, 3)) >= ST1OIband) Then
If rng Is Nothing Then
Set rng = xVal
Else
Set rng = Union(rng, xVal)
End If
End If
Next xVal
MsgBox rng.Address
If Not rng Is Nothing Then rng.EntireRow.Delete
On Error GoTo 0

End Sub