VBA find

ericlch16

Active Member
Joined
Nov 2, 2007
Messages
279
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?
 
Last edited:

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

b.downey

Active Member
Joined
Oct 16, 2011
Messages
484
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.


Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,962
Messages
5,599,065
Members
414,281
Latest member
Engjamal2021

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