VBA Column Finder

[Soiklug]

Hi All,

I'm new to this forum so I apologize if the question has already been answered. I am working on a spreadsheet that fills out shipping details to a customer. What I am looking to do is execute a VBA code that will locate the column and perform the vlookup. Typically, I would just create a macro of me filling it out and calling it good, but with the number of customers slightly changing the order of their columns it would be nice if I had a code that looked for the appropriate column and ran from there. Any ideas on how I should approach this or does someone have a code they have had success with?

Thank you

 

[HighAndWilder]

Hi All,

I'm new to this forum so I apologize if the question has already been answered. I am working on a spreadsheet that fills out shipping details to a customer. What I am looking to do is execute a VBA code that will locate the column and perform the vlookup. Typically, I would just create a macro of me filling it out and calling it good, but with the number of customers slightly changing the order of their columns it would be nice if I had a code that looked for the appropriate column and ran from there. Any ideas on how I should approach this or does someone have a code they have had success with?

Thank you

This function will return the column number based upon the column header value.

Public Function fncFindColumnNumber(strWorksheet As String, strColumnHeader As String) As Long
Dim rngFound As Range

    Set rngFound = Worksheets(strWorksheet).Range("1:1").Find(strColumnHeader, LookIn:=xlValues)
    
    If Not rngFound Is Nothing Then
        fncFindColumnNumber = rngFound.Column
    Else
        fncFindColumnNumber = 0
    End If
    
End Function

Call it like this where 'ShippingDetails' is the worksheet name and 'PhoneNumber' is the column header

  intColumn = fncFindColumnNumber("ShippingDetails", "PhoneNumber")

    If intColumn <> 0 Then
        ' Put Vlookup code here.
    Else
        ' Put code to execute when column not found here.
    End If