Check if cell found is in column AP

0 votes

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

enter image description here

Apr 4, 2023 in Others by narikkadan
• 63,420 points
390 views

1 answer to this question.

0 votes

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
answered Apr 4, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

How to check if a cell is empty in a range variable?

Use WorksheetFunction.CountA() (https://learn.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.counta) function If WorksheetFunction.CountA(rng) = 0 Then ...READ MORE

answered Jan 15, 2023 in Others by narikkadan
• 63,420 points
467 views
0 votes
1 answer

Is there a way to quickly check what attribute the data is in a cell in a spreadsheet?

Excel will automatically make assumptions about the ...READ MORE

answered Jan 8, 2023 in Others by narikkadan
• 63,420 points
279 views
0 votes
1 answer

Formula to count if a text is written in a cell

Use SCAN, like this: =ArrayFormula(IF(A1:A10="",,SCAN(-1,A1:A10,LA ...READ MORE

answered Feb 4, 2023 in Others by narikkadan
• 63,420 points
201 views
0 votes
1 answer

Highlight cells in an Excel column if the value can be found in an array

Try this: =COUNTIFS(B:E,$A1) READ MORE

answered Feb 24, 2023 in Others by narikkadan
• 63,420 points
803 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
838 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,139 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
434 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
700 views
0 votes
1 answer

Check multiple cells in excel, move to next cell if null and if value found stop and return that value

Use this formula: =INDEX(FILTER(A2:D2,A2:D2<>""),1,1) INDEX returns the first value ...READ MORE

answered Mar 24, 2023 in Others by Kithuzzz
• 38,010 points
256 views
0 votes
1 answer

Excel formula to get certain cell if the value is between 2 numbers

So, first with vlookup(): A formula so you ...READ MORE

answered Feb 11, 2023 in Others by Kithuzzz
• 38,010 points
653 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP