Find and find next

AC PORTA VIA

Board Regular
Joined
Apr 9, 2016
Messages
120
Office Version
  1. 365
Platform
  1. Windows
trying to find code to look for input box value and offset input box value in other column as well as time stamp and user in other offset columns
however if find value have date in offset column look in other row or find next as long as there is not date in offset value
so if I am looking for 23907 it should find same value in row 6 since other two values in column 1 and 5 already have date in column AQ
 

Attachments

  • find.png
    find.png
    21.3 KB · Views: 8

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
386
Office Version
  1. 365
Platform
  1. Windows
Finding all of the cells with 23907, for example, is really easy if you import the module file from Chip Pearson's website. In the introduction on the page, you will find a link to a zip file with a .bas file that you can import into your workbook. Once you unzip the file, you can drag and drop it into the VBAProject for your workbook or us File->Insert file to find it. It will put in a module called modFindAll64 into your VBE project. I use this module in many projects because it is so easy.

An example of using it in a project based on your request is below. In another module, I put the following code based on your picture, and it found AI6 as the target cell to use with no problems.

VBA Code:
Sub FindRow()
    Dim c As Range, cFound As Range
    Dim SearchTerm As String
   
    SearchTerm = "23097"
    Set cFound = FindAll(Range("AI:AI"), SearchTerm, , xlPart)
    For Each c In cFound
        If Cells(c.Row, Range("AQ1").Column).Value = "" Then
            Exit For
        End If
    Next
    If Not c Is Nothing Then
        'do whatever you need here with the row
        'c is the cell with the searchterm in the row that has the blank date
    End If
End Sub
 
Solution

AC PORTA VIA

Board Regular
Joined
Apr 9, 2016
Messages
120
Office Version
  1. 365
Platform
  1. Windows
Finding all of the cells with 23907, for example, is really easy if you import the module file from Chip Pearson's website. In the introduction on the page, you will find a link to a zip file with a .bas file that you can import into your workbook. Once you unzip the file, you can drag and drop it into the VBAProject for your workbook or us File->Insert file to find it. It will put in a module called modFindAll64 into your VBE project. I use this module in many projects because it is so easy.

An example of using it in a project based on your request is below. In another module, I put the following code based on your picture, and it found AI6 as the target cell to use with no problems.

VBA Code:
Sub FindRow()
    Dim c As Range, cFound As Range
    Dim SearchTerm As String
 
    SearchTerm = "23097"
    Set cFound = FindAll(Range("AI:AI"), SearchTerm, , xlPart)
    For Each c In cFound
        If Cells(c.Row, Range("AQ1").Column).Value = "" Then
            Exit For
        End If
    Next
    If Not c Is Nothing Then
        'do whatever you need here with the row
        'c is the cell with the searchterm in the row that has the blank date
    End If
End Sub
thanks for your respond
your code errors out on this line highliting FindAll
VBA Code:
Set cFound = FindAll(Range("AI:AI"), SearchTerm, , xlPart)
Sub or Function not defined
 

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
386
Office Version
  1. 365
Platform
  1. Windows
Did you insert the module from the link I referenced?
 

Forum statistics

Threads
1,141,413
Messages
5,706,298
Members
421,440
Latest member
cmphares

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top