I am bogged down on how to use this formula...

=COLUMN(INDEX(B1:CZ1,MATCH(A2,B1:CZ1,0)))

To set these lines to the correct column number...

aScan.Cut Sheets("FBAout").Range("B" & Rows.Count).End(xlUp)(2)
Sheets("FBAout").Range("B" & Rows.Count).End(xlUp).Offset(, 1) = Date

Where the column number (2 to 104) can replace the "B".

Cell A2 on FBAout sheet is a drop down with 50+ sheet names.
I have MySheet working for the proper sheet to FIND the search value, but I need to return it to the column header holding the same sheet name as in the drop down in A2.

Can't figure how to use the formula value or write the code to the correct proper column on sheet FBAout.

I'm using a drop down because the sheet names are everything from a simple name like "X" to "Brown & 80%White Calfskin" so an input box and proper spelling would be a nightmare!

Thanks,
Howard


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A2")) Is Nothing Or Target.Cells.Count 1 Then Exit Sub
Application.EnableEvents = False


Dim LRow As Long
Dim aScan As Range 'look for this value in one of 50+ different sheets (A:B columns)
Dim cScan As String 'is sheet FBAout A5 scanned-in value
Dim MySheet As String


cScan = Sheets("FBAout").Range("A5")
MySheet = Worksheets("FBAout").Range("A2") ' drop down with all sheet names in CELL FBAout A2


If cScan = "" Then
Exit Sub
ElseIf IsNumeric(cScan) Then
cScan = Val(cScan) '/ converts a "text" number to a value
Else
'/ is text and that is okay
End If


With Sheets(MySheet)

LRow = Sheets(MySheet).Cells(Rows.Count, "A").End(xlUp).Row

Set aScan = Sheets(MySheet).Range("A2:B" & LRow).Find(What:=cScan, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If Not aScan Is Nothing Then
aScan.Cut Sheets("FBAout").Range("B" & Rows.Count).End(xlUp)(2)
Sheets("FBAout").Range("B" & Rows.Count).End(xlUp).Offset(, 1) = Date

ElseIf aScan Is Nothing Then
MsgBox " No match found."

End If

End With

Application.EnableEvents = True

End Sub