VBA find

[ericlch16]

In a sheet i have these columns

A B C D E
41667228 xxxxxx 5654645 51555567 dsfsdfsd
41667223 vvvvvv 8888889 fdsfsdf sdfsdf
41667232 45672828 7777777 sdfdsfs 45435435
41667225 89999999 7656546 21432423 45435435

I am writing a VBA macro so that it finds out the column where the user is inputing the invoice no. an invoice no is 8 digit number and always starts with a 4. in the above, the invoice number is in column A but the data entry can be random. sometimes the user puts it in column D. sometimes in in E. sometimes the column header starts on row 8. also there can be more columns.

any idea how to approach this?

should i loop in each column? but how to determine which column has the invoice number? I need to make sure that all rows in that column contains a number which start with a 4 and is of 8 digit length.

any advice?

 

[b.downey]

The code will identify the row and column that contains the invoce number.

It steps thru each a the cells in the used range. When the isInvoice function returns True, you have identified a valid invoice. you just need to decide where the results go.

Option Explicit
Sub Process()
    Dim ws As Worksheet
    Dim rng As Range
    
    Dim RowNo  As Integer
    Dim ColNo As Integer
    
    Set ws = ThisWorkbook.Worksheets(1)
    Set rng = ws.UsedRange
    
    For RowNo = 1 To rng.Rows.Count
        For ColNo = 1 To rng.Columns.Count
            Debug.Print ws.Cells(RowNo, ColNo).Address, ws.Cells(RowNo, ColNo), isInvoice(ws.Cells(RowNo, ColNo))
        Next ColNo
    Next RowNo
End Sub
Function isInvoice(s As String) As Boolean
    Dim n As Long
    
    n = Val(Trim(s))
    
    If (n >= 40000000) And (n <= 50000000) Then
        isInvoice = True
    Else
        isInvoice = False
    End If
    
End Function