find value even if filter is on

[hrm1220]

I'm not sure if VBA for Excel has this or not, but I'm trying to find a way to find a given value regardless if the filter is on or off. I’m trying to make the code below run faster and hoping that find will be faster then the do…loop code I have below (MAX_AREA_ROWS is in my globals module):

 Function ModFTQ(qty, X, Y, Z)
Application.Volatile
X= X& Y
Application.Volatile

With CEIDData

If .FilterMode = False Then
   Set j = .Cells.Find(X, LookIn:=xlValues)
    j = j.Row
   
Else:
j = 1
  [b]Do While .Cells(j, Z.Column).Value <> X And j < MAX_AREA_ROWS
  j = j + 1
  Loop[/b]
End If
    If qty < .Cells(j, 4).Value Then
     ModFTQ = .Cells(j, 4).Value
    ElseIf qty > .Cells(j, 5).Value And .Cells(j, 5).Value <> "-1" Then
     ModFTQ = .Cells(j, 5).Value
    Else: ModFTQ = qty
    End If
End With
End Function

 

[SkipVought]

Hi,

Why not if the filter is on, save the filter criteria, remove the filter, do your find, restore the filter & criteria.

Skip,

_[red][/red]

 

[hrm1220]

I appreciate the input. Currently, there are at least 400 cells with this function in each of them. If I do what you are stating, I believe it would take more time, correct?

 

[SkipVought]

Is this a function that you plan to use on the SHEET?

In that case it cannot do what I suggested.

If you have 400 of these on a sheet, your recalculation might take a few seconds.

Skip,

_[red][/red]

 

[hrm1220]

Yes, this will be used on the sheet. Whenever someone changes a cell on the sheet, it will recalculate itself and 5-7 is too long and will irritate the enduser. Any suggestions are appreciated.

 

[PHV]

Have you tried this ?
j = .Cells.SpecialCells(xlCellTypeVisible).Find(X, LookIn:=xlValues).Row


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

OOps, sorry, misunderstood the issue :~/

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886