If partial match in first row of visible row

[julhs]

I’ve tried various different ways to achieve what I’m trying to do.
What I am trying to do is establish if “FrwD + 2” of the filtered range contains a string starting with “To*”
ie “To whoever”
The code below is not right but the nearest I’ve come up with, it is returning the ACTUAL FrwD + 2 of the HIDDEN filtered range; NOT the FrwD + 2 row of the VISIBLE range.
I am making a hash of this; can someone give me some guidance?

Public Sub WhichFilter()
Option Explicit
Option Compare Text
Dim sht As Worksheet
Dim rng As Range
Dim FrwD As Long
Set sht = ThisWorkbook.ActiveSheet
Set rng = Range("AK:AK").Find(what:="Paid", LookIn:=xlValues, LookAt:=xlWhole)
FrwD = rng.Row
If Not Left(CStr(Range("AS" & FrwD + 2)), 2) = "To*" Then 'This is giving FrwD + 2 of HIDDEN filtered range and NOT the FrwD + 2 of the visible first range
MsgBox "Your attempting to use the WRONG Sub on this filter - Abort macro", vbOKOnly Or vbCritical, Application.Name
Exit Sub
End If

If true continue sub…….

 

[DanteAmor]

Hi @julhs , thanks for posting on the MrExcel board.

Should be = "To" without the asterisk, since you are validating only 2 letters.

Try this:

Option Explicit
Option Compare Text

Public Sub WhichFilter()
  Dim sht As Worksheet
  Dim rng As Range
  Dim FrwD As Long
  Dim i As Long, n As Long
  
  Set sht = ThisWorkbook.ActiveSheet
  Set rng = Range("AK:AK").Find(what:="Paid", LookIn:=xlValues, LookAt:=xlWhole)
  If Not rng Is Nothing Then
    FrwD = rng.Row
    For i = FrwD + 1 To Range("AS" & Rows.Count).End(3).Row
      If Rows(i).Hidden = False Then
        n = n + 1
        If n = 2 Then
          If Not Left(CStr(Range("AS" & i)), 2) = "To" Then 'This is giving FrwD + 2 of HIDDEN filtered range and NOT the FrwD + 2 of the visible first range
            MsgBox "Your attempting to use the WRONG Sub on this filter - Abort macro", vbOKOnly Or vbCritical, Application.Name
            Exit Sub
          Else
            'If true continue sub
          
          End If
        End If
      End If
    Next
  Else
    MsgBox "Paid not exists"
  End If
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[Jeffrey Mahoney]

Somebody else might have a cleaner option, but here goes

Public Sub WhichFilter()

  Dim sht As Worksheet
  Dim rng As Range
  Dim FrwD As Long
  Dim VisRng As Range
  Dim Cel As Range
  Dim ToCel As Range
  Dim X As Long
  
  Set sht = ThisWorkbook.ActiveSheet
  Set rng = Range("AK:AK").Find(what:="Paid", LookIn:=xlValues, LookAt:=xlWhole)
  If rng Is Nothing Then Exit Sub     'No paid cells found
  FrwD = rng.Row
  
  Set VisRng = Range(sht.Cells(FrwD + 1, 45), sht.Cells(10000, 45)).SpecialCells(xlCellTypeVisible)
  X = 0
  For Each Cel In VisRng
    X = X + 1
    If X = 2 Then
      Set ToCel = Cel
      Exit For
    End If
  Next Cel
  If Not Left(ToCel.Value, 3) = "To " Then 'This is giving FrwD + 2 of HIDDEN filtered range and NOT the FrwD + 2 of the visible first range
    MsgBox "Your attempting to use the WRONG Sub on this filter - Abort macro", vbOKOnly Or vbCritical, Application.Name
    Exit Sub
  End If

'If true continue sub…….

End Sub

 

[julhs]

Thank you both, will test asap.

 

[julhs]

Well I am spoilt for choice, both work for me.
Danta quick question, what is the the significance of the (3) in this line

For i = FrwD + 1 To Range("AS" & Rows.Count).End(3).Row

 

[DanteAmor]

what is the the significance of the (3)

The number 3 is the numeric value for the xlUp parameter:

You can use xlUp or 3:

xlDown = 4
xlToLeft = 1
xlToRight = 2
xlUp = 3

 

[Jeffrey Mahoney]

That's funny (strange). That must be a leftover from the past. MS gives these values

 

[julhs]

Danta.
Thank you for that.

 

[julhs]

Jeffery.
"Left over from past" I assume you mean pre 365 (so my 2010 or Danta's 2013 version)??

 

[Jeffrey Mahoney]

Jeffery.
"Left over from past" I assume you mean pre 365 (so my 2010 or Danta's 2013 version)??

I don't know when MS changed the enumeration values for those. They usually allow users to use old values for a long time. Either of those can be used.