[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".

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

[Claus Busch]

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

[L. Howard]

Hi Claus,

Works great. A little mix up with Target cell and drop down cell, simple switch and BINGO!

That match column method rings familiar, now that I see it again. Dang, I gotta remember those things.

The OP is really looking for whatever layout I feel is appropriate. So with 100+ columns, most are half-a-mile to the right off screen. I had a so-so method where if the header (sheet name) in the drop down was off screen, say about column 14 or so, then columns 3 to 14 would be hidden. But that was using a separate cell in column a for every sheet.

Kind of like this code you offered a few days ago on a different issue. But with just the two cells for sheet name and search value I am not using it..

Range("C1:Y1").Interior.Color = xlNone

If Intersect(Target, Range("A2:A13")) Is Nothing Or Target.Count 1 Then Exit Sub

Cells(1, Target.Row * 2 - 1).Interior.Color = vbYellow

Is there a reasonable way to ..bring the destination column into the viewing screen area once it is selected in the drop down? That way the user can see the numerous scan returns and dates as they occur.

Howard

[Claus Busch]

Hi Howard,

Am Sat, 5 Mar 2016 04:40:37 -0800 (PST) schrieb L. Howard:

Is there a reasonable way to ..bring the destination column into the viewing screen area once it is selected in the drop down? That way the user can see the numerous scan returns and dates as they occur.

try it into the IF statement:

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
ActiveWindow.ScrollColumn = myCol

Else
MsgBox " No match found."

End If

ActiveWindow.ScrollColumn brings the destination column to the left
side of the table.
If you need more help, please send me the file with comments.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[L. Howard]

try it into the IF statement:

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
ActiveWindow.ScrollColumn = myCol

Else
MsgBox " No match found."

End If

ActiveWindow.ScrollColumn brings the destination column to the left
side of the table.
If you need more help, please send me the file with comments.




Hi Claus,
That does a nice job. Works pretty slick.

Thanks for the excellent help. Appreciate it.

Howard

[Claus Busch]

Hi Howard,

Am Sat, 5 Mar 2016 05:37:49 -0800 (PST) schrieb L. Howard:

That does a nice job. Works pretty slick.

you are welcome.

You can fix the first column that it stays in visible range.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[Claus Busch]

Hi Howard,

Am Sat, 5 Mar 2016 14:46:46 +0100 schrieb Claus Busch:

You can fix the first column that it stays in visible range.

sorry, wrong expression:
You can freeze the first column


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[L. Howard]

sorry, wrong expression:
You can freeze the first column


Regards
Claus B.

Hi Claus,

Yes, that is what I did. Freeze column A and now the current column becomes the adjacent column with the first return of data.

Hit Home key to refresh screen view.

Thanks again.

Howard

[L. Howard]

Hi again Claus,

https://www.dropbox.com/s/wvfcp4mwa4...pBox.xlsm?dl=0

Having trouble with my code. Some comments on the sheet FBAout.

There are two (and sometimes three) columns on the search sheets (all the sheets except FBAout) that the code is to find the "scanned" entry in cell A5 and return to the proper header on FBAout and data stamp in the adjacent column.

I had previous code doing that perfectly, except there was the concern that the lookup range was dependent on the column length of column A on the search sheet. Whereas, column A could be only a few rows and other columns could be many, many rows. So if the lookup value was in a longer column than A, then it would be "Not Found".

I installed some code you previously wrote which finds the longest column within the columns search range, A B C in this case, and I cannot find what my error is.

It just doesn't look in the search sheet??

Its a 50 + sheet workbook, but using only sheet "CN1" as the search sheet will be enough to straighten out my mistaken code.

Thanks for taking a look.

Howard

[L. Howard]

Hi Claus,

I found my mistakes, seeks I was using improper sheet naming conventions with in this With statement. I was not including Sheets(MySheet). where needed.

Seems to work fine now.


With Sheets(MySheet)

myFirst = Sheets(MySheet).Columns("A").Column
myLast = Sheets(MySheet).Columns("C").Column
ReDim Preserve varRows(myLast - myFirst)


' Finds the row number of the longest column between A and C
For i = myFirst To myLast
varRows(i - myFirst) = Sheets(MySheet).Cells(Rows.Count, i).End(xlUp).Row
Next


Set aScan = Sheets(MySheet).Range("A3:C" & Application.Max(varRows)).Find(cScan, LookAt:=xlWhole)


If Not aScan Is Nothing Then

myCol = Application.Match(MySheet, Sheets("FBAout").Range("1:1"), 0)
ActiveWindow.ScrollColumn = myCol

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

Howard