VBA identify first time an account is above 2999999

[jakobt]

Do have an increasing list of numbers (account numbers) in column B.

I want to write a variable to identify the row number on the account first time the listed account number is above 2999999.

The variable can be called "Marginrow".

 

[Logit]

.
Here is a formula approach.

Considering you are using Sheet2. In cell D1 enter this formula :

=IFERROR(MATCH(C1,Sheet2!B:B,0),"Enter Amt")

Enter the amount you are seeking in C1, the row number will display in D1.

If C1 is left blank, D1 will display "Enter Amt"

 

[Logit]

.
Here is a VBA macro approach :

Option Explicit


Sub FindFirstMatchColumnA()
     
    Dim SearchString As String
    Dim FoundCell As Range
     
    SearchString = InputBox("string to find here")
     
    With Sheets("Sheet1").Range("B:B") 'change Sheet1 to suit
        Set FoundCell = .Cells.Find(What:=SearchString, _
        After:=.Cells(.Cells.Count), _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False)
         
        If Not FoundCell Is Nothing Then
        MsgBox FoundCell.Address
        FoundCell.Select
        Else
            MsgBox "Search String not found"
        End If
    End With
     
End Sub

 

[jakobt]

Thanks what does option explicit mean?

 

[Logit]

.
Option Explicit forces the programmer to declare variables.

https://docs.microsoft.com/en-us/do...eference/statements/option-explicit-statement