With Frozen panes move to specific column based on cell value

John_Wh

Board Regular
Joined
Feb 26, 2013
Messages
64
In the below example the columns left of "L" are frozen. Beginning in L1 => CU1 have game number 1 - 82. In cell H1 is the next game number. I there a way based on the value in L1 to advance to the corresponding column?

So in this case the next game is #17 so I want first seen column right of K to be AC. (The column with 17 in cell AC1)

Thanks


 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,494
Office Version
365
Platform
Windows
How about
Code:
Sub NextGame()
ActiveWindow.ScrollColumn = Range("H1")
End Sub
 

John_Wh

Board Regular
Joined
Feb 26, 2013
Messages
64
Slightly mis stated, I want the value in H1 to be the value that determines which column is right of "K". It would move the column with 17 (which is in cell C1) to determine which column would move to be just right of "K"

Fluff posted as I typed this
 
Last edited:

John_Wh

Board Regular
Joined
Feb 26, 2013
Messages
64
That returns column Q which is the 17th row beginning at A. I tried to add Range "H1"+12 but that does not work. Guess a work around might be to take the value in H1 and add 12 and put that value in another cell and use it as the base. Which does work, but it did change Calculations Options to Manual for whole workbook, so I changed it back to Automatic.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,494
Office Version
365
Platform
Windows
Try
Code:
ActiveWindow.ScrollColumn = Range("H1") + 12
 

John_Wh

Board Regular
Joined
Feb 26, 2013
Messages
64
Yes, that works too and is more straight forward than my work around.

Thanks again
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,494
Office Version
365
Platform
Windows
You're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,095,227
Messages
5,443,212
Members
405,219
Latest member
CraneS

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top