Set the copy to column to a variable
Hi Howard,
Am Sat, 5 Mar 2016 00:30:07 -0800 (PST) schrieb L. Howard:
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".
try it this way (Code in module of "FBAout"):
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
On Error GoTo CleanUp
Dim LRow As Long
Dim aScan As Range 'look for this value in one of 50+ different sheets
(A:B columns)
Dim cScan As Variant 'is sheet FBAout A5 scanned-in value
Dim MySheet As String
Dim myCol As Long
Dim dest As Range
cScan = Range("A5")
MySheet = Target ' drop down with all sheet names in CELL FBAout A2
If cScan = "" Then
Exit Sub
End If
With Sheets(MySheet)
LRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set aScan = .Range("A2:B" & LRow).Find(What:=cScan, _
LookIn:=xlValues, LookAt:=xlWhole)
If Not aScan Is Nothing Then
myCol = Application.Match(MySheet,
Sheets("FBAout").Range("1:1"), 0)
Set dest = Sheets("FBAout").Cells(Rows.Count,
myCol).End(xlUp)(2)
dest.Offset(0, 1) = Date
aScan.Cut dest
Else
MsgBox " No match found."
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub
Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
|