Check if cell found is in column AP

Question

I have a macro that searches a certain Range for a code that was entered using a Userform. then determines the value that is the closest to the right from the matching cell in that range (only the QUANTITY columns will be found in order to use them for other calculations). The sheet, however, is made for the 12 months of a year, and after the last month, there is another column with a formula. I want the "TOTAL" ("AP") column and the subsequent ones to remain unaffected by the code.

The code I'm working with is as follows (please note that the code continues, but I don't think the other portions are important):

Private Sub CommandButton1_Click()

   Dim cell As Range

     'FINDS THE MATCHING CODE FROM THE USERFORM
     Set cell = Sheets("TEST").Range("C6:C42").Find(What:=TextBox1, LookIn:=xlFormulas, _
     LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
     MatchCase:=True, SearchFormat:=False)

   'IF SOMETHING IS FOUND THEN...
   If Not cell Is Nothing Then

     If cell.End(xlToRight).Column = AP Then  'HERE I AM HAVING TROUBLE FIGURING HOW TO DECLARE THE COLUMN

       MsgBox "AP"  'DO SOMETHING...

     End If

'MORE HERE

Answer 1

Find the Last Cell in the Row of a Found Cell

Option Explicit

Private Sub CommandButton1_Click()

    ' Always reference the workbook. Sure, the correct workbook will be active
    ' when you click the button, but what if everything is correct
    ' and you test the code from VBA and it is repeatedly failing
    ' (because a different workbook is active)?
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Use variables to avoid long unreadable lines.
    ' That's one of the reasons they're here for.
    Dim ws As Worksheet: Set ws = wb.Sheets("TEST")
    Dim rg As Range: Set rg = ws.Range("C6:C42")
    
    Dim fCell As Range
    
    ' Study the Find method's arguments and their parameters:
    ' 5.) you have a single column range, it doesn't matter if you search
    '     by rows or by columns ('SearchOrder')
    ' 6.) the default parameter is 'xlNext' which is also irrelevant
    '     since you have unique values in the column so either the value
    '     will be found or not ('SearchDirection')
    ' 7.) matching the case is not necessary when dealing with numbers:
    '     there is no lowercase or uppercase number ('MatchCase')
    ' 9.) you need to explicitly set the search format to True for it
    '     to actually become True ('SearchFormat')
    ' 2.) Note that you have omitted the 'After' argument whose default
    '     parameter is the first cell of the range. As said, it doesn't
    '     matter because you have unique values but with this setup
    '     the search will be performed in the following order:
    '     'C7, C8 , C9... C41, C42, C6' (no typo here)
    
    ' Attempt to find the matching code from the user form
    Set fCell = rg.Find(What:=TextBox1, LookIn:=xlFormulas, LookAt:=xlWhole)

    Dim lCell As Range
    
    If Not fCell Is Nothing Then
        ' Here you could again use the Find method
        ' but I opted for the End property.
        ' You search in the same row, from column 'AP' to the left:
        Set lCell = fCell.EntireRow.Columns("AP").End(xlToLeft)

        If fCell Is lCell Then ' you came back to the first (found) cell
            MsgBox "No data in row.", vbExclamation
        Else
            MsgBox "Last cell: """ & lCell.Address(0, 0) & """.", vbInformation
        End If

    Else

        MsgBox "The ID """ & TextBox1 & """ was not found.", vbCritical        
    
    End If

End Sub