View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Claus Busch Claus Busch is offline
external usenet poster
 
Posts: 3,872
Default 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