Using >= in the Find method to find all values greater than $200 in column E. (i.e., ">=$200" - which is wrong)

[chazrab]

As stated. This code works only if the exact amount between the quotes is used.("$200") I want the code to find all values greater than or equal to $200.
using > =.
I get no error message but no results with this code:

Dim x As String, c As Range, rw, FirstAddress As Variant, ws As Worksheet
   Set ws = Worksheets("AOS")
       Application.EnableEvents = False
       Application.ScreenUpdating = False
 With ws.Range("E2:E1000")
     Set c = .Find(">=200", LookIn:=xlValues)  ---> this gives 0 results when I know there are at least 20 values greater than or equal to $200 in the column
        If Not c Is Nothing Theno
           rw = 2
           FirstAddress = c.Address
             Do
                c.Select
                Range(Cells(c.Row, 1), Cells(c.Row, 4)).copy Destination:=Sheets("REPORT").Range("A" & rw)
                rw = rw + 1
                Set c = .FindNext(c)
             Loop While Not c Is Nothing And c.Address <> FirstAddress
        Else
        MsgBox "Code wrong"
        End If
    End With

I know its probably a matter of correctly placing quotes and/or astericks. Do not want to use Autofiler code.

Thanks for anyone's help.
cr

 

[6StringJazzer]

The Find method cannot do relational numerical comparisons. It can do exact matches, or wildcard matches. You will have to loop through each cell in the range.

 

[Peter_SSs]

Do not want to use Autofiler code.

1. Why not?
2. Would Advanced Filter be acceptable instead?

Perhaps you could try this with a copy of your workbook if you want to transfer all results at once.
I have assumed

• There is nothing in 'REPORT' column E
• There is nothing in column Z of 'AOS'

BTW, when posting your code in the forum, please post the whole code (so we can see if it an 'Event' procedure, normal Sub etc) and please copy/paste your actual code. You said the above code is working code if using an exact amount but the code is not valid code so couldn't be working even if it had an exact value.

Sub Test()
  Dim ws As Worksheet
  Dim rCrit As Range
 
  Set ws = Worksheets("AOS")
  Application.EnableEvents = False
  Application.ScreenUpdating = False
  Set rCrit = ws.Range("Z1:Z2")
  rCrit.Cells(2).Formula = "=E2>=200"
  ws.Range("A1:E1000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rCrit, CopyToRange:=Sheets("REPORT").Range("A2"), Unique:=False
  rCrit.ClearContents
  With Sheets("REPORT")
    .Range("E2", .Range("E" & Rows.Count).End(xlUp)).ClearContents
    .Range("A2:D2").Delete Shift:=xlUp
  End With
  Application.EnableEvents = True
  Application.ScreenUpdating = True
End Sub

 

[chazrab]

1. Why not?
2. Would Advanced Filter be acceptable instead?

Perhaps you could try this with a copy of your workbook if you want to transfer all results at once.
I have assumed

• There is nothing in 'REPORT' column E
• There is nothing in column Z of 'AOS'

BTW, when posting your code in the forum, please post the whole code (so we can see if it an 'Event' procedure, normal Sub etc) and please copy/paste your actual code. You said the above code is working code if using an exact amount but the code is not valid code so couldn't be working even if it had an exact value.

Sub Test()
  Dim ws As Worksheet
  Dim rCrit As Range
 
  Set ws = Worksheets("AOS")
  Application.EnableEvents = False
  Application.ScreenUpdating = False
  Set rCrit = ws.Range("Z1:Z2")
  rCrit.Cells(2).Formula = "=E2>=200"
  ws.Range("A1:E1000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rCrit, CopyToRange:=Sheets("REPORT").Range("A2"), Unique:=False
  rCrit.ClearContents
  With Sheets("REPORT")
    .Range("E2", .Range("E" & Rows.Count).End(xlUp)).ClearContents
    .Range("A2:D2").Delete Shift:=xlUp
  End With
  Application.EnableEvents = True
  Application.ScreenUpdating = True
End Sub

Hi Peter, thanks for helping. I tried your code.
Run time error "The extract range has a missing or invalid field name" at this line:

  ws.Range("A1:E1000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rCrit, CopyToRange:=Sheets("REPORT").Range("A2"), Unique:=False

snip image below. I tried using another helper's For looping code to do this. It works, but is extremely slow:

Private Sub cmdCOPYVALS2_Click()
Sheets("REPORT").Range("A1:G700").ClearContents
Dim wsSource As Worksheet
    Dim wsDest As Worksheet
    Dim lastRow As Long
    Dim i As Long
   'Set the source and destination worksheets
    Set wsSource = Worksheets("AOS")
    Set wsDest = Worksheets("REPORT")
   ' Find the last row in column E
    lastRow = wsSource.Cells(wsSource.Rows.Count, "E").End(xlUp).Row
    ' Loop through each row in column E
    For i = 2 To lastRow
        If wsSource.Cells(i, "E").Value >= 200 Then
            ' Copy the entire row to the destination sheet
            wsSource.Rows(i).Copy Destination:=wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
    Next i
UserForm1.Show
End Sub

No problem for me to use Autofilter on this. It just did not capture all values all the time in code I've written in past applications

Will use your code, i have no idea what the run time error means.
Thanks again for all your help.
cr

 

[RoryA]

The destination range either needs to be completely blank, or have headers that exactly match the source data headers.

 

[chazrab]

The destination range either needs to be completely blank, or have headers that exactly match the source data headers.

The destination range is the sheet named REPORT and is completely blank with no headers.
This first line of code takes care of this:

Sheets("REPORT").Range("A1:G700").ClearContents

 

[chazrab]

To PeterSS and Rory:
In an effort to rely on the Message Board as little as possible, although through the years I have learned valuable things from the Message Board. , I came up with this solution using Autofilter and it seems to work faster than a For Loop. My goal: as little code as possible that will do the job accurately and quickly.

Private Sub cmdAUTOFILTERTEST_Click()
Sheets("REPORT").Range("A1:z700").ClearContents
Dim wsSource, wsDest As Worksheet, lastRow, i As Long
    Set wsSource = Worksheets("AOS")
    Set wsDest = Worksheets("REPORT")
    lastRow = wsSource.Cells(wsSource.Rows.Count, "E").End(xlUp).Row
    Sheets("AOS").Range("A1:F1").AutoFilter
    With Sheets("AOS").Range("B1").CurrentRegion.Offset(3, 0)
       .AutoFilter Field:=5, Criteria1:=">=" & 200
       .SpecialCells(xlVisible).Copy Sheets("REPORT").Range("A1")
    .AutoFilter
    End With
UserForm1.Show
End Sub

Thanks to both of you for all your help.
cr
Kingwood, Tx

 

[Peter_SSs]

I came up with this solution using Autofilter

Hmm, you originally said you did not want to use that.

My goal: as little code as possible that will do the job accurately and quickly.

For the future I would recommend omitting the red part. It is not uncommon to have longer code do the required job much faster than a short code.

 

[chazrab]

Hmm, you originally said you did not want to use that.


For the future I would recommend omitting the red part. It is not uncommon to have longer code do the required job much faster than a short code.

Will do. I had a change of heart about not using Autofilter. At least I can use inequality symbols with criteria using Autofilter.
Thanks again for your help.
cr