I have one workbook with many order numbers in many different columns(20+). Each Column has a different Header(column name) that can be found in the first row.
On a different sheet I will have all of the order numbers compiled into column A. In column B I would populate the name of the column (header) for each order number.
The code below would provide me a function to return the sheet name. In line 14 I tried changing w.s. name to header but it would not work.
Option Explicit
Function FindMyOrderNumber(strOrder As String) As String
Dim ws As Worksheet
Dim rng As Range
Application.Volatile
For Each ws In Worksheets
If ws.CodeName <> "Sheet3" Then
Set rng = Nothing
On Error Resume Next
Set rng = ws.Cells.Find(What:=strOrder, LookAt:=xlWhole)
On Error GoTo 0
If Not rng Is Nothing Then
FindMyOrderNumber = ws.Name
Exit For
End If
End If
Next
Set rng = Nothing
Set ws = Nothing
End Function
FindMyOrderNumber = ws.Cells(1, rng.Column)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments