Excel - Selecting a row by Date 2

[shytott]

Hi All
Skipvought penned me a great 5 line macro a year or so ago which has worked perfectly since but now I need to adapt it to a revamped spreadsheet. (thanks again Skip) The spreadsheet has the top 23 rows frozen for a title section and below is the scrollable area of a further 365 rows covering a full year. Each row starts with the date in col B (ie 1 Jan is in B24) and has data in the following 31 cols. The macro allows me to click a button which will bring Todays row into view just below the frozen section and works with reference to: Now().
What I would like to now do, is exactly the same task, but instead of relating my target row to todays date, I would like it to reference a date input into cell K19. My idea is to be able to shuffle the date in K19 back and forth by using a scroll bar button (which I have already successfully done) but Id also like the main grid to move at the same time in tandem with this so that the target row of data is now showing just below the frozen section. (hope that made sense, Ive rewritten it 3 times!).
Skips original macro is:
Sub GotoX()
Range("A24").Select
ActiveWindow.FreezePanes = True
x = 24 + Int(Now()) - Range("b24")
Range("b24").End(xlDown).Select
Application.Goto Reference:="R" & x & "C2"
End Sub

I've tried substituting Int() for K19 as in:
x = 24 + Int(K19) - Range("b24")
but it crashes in the last line of the macro and stops at 31 Dec.

Many Thanks
Shytott

 

[PHV]

I'd try this:
x = 24 + Range("K19") - Range("b24")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Hi,

Skips original macro is:
Sub GotoX()
Range("A24").Select
ActiveWindow.FreezePanes = True
x = 24 + Int(Now()) - Range("b24")
Range("b24").End(xlDown).Select
Application.Goto Reference:="R" & x & "C2"
End Sub

I'd wager that this is definitely NOT any code that I wrote.

I don't use Goto.

You might try something like this, however...

Sub GotoX()
    Range("A24").Select
    ActiveWindow.FreezePanes = True
    x = 24 + Int(Now()) - Range("b24")
    Cells.find(x).select
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shytott]

Thanks for the replies guys - (sorry about the late response).
PHV, I tried yours and it almost worked - if I scroll any date input to K19 and click a button with your change, it will go to that date but ONLY when I activate the macro. What I was hoping to achieve was for the whole bottom section to scroll AS I changed the date using the scroll button. So basically, is it possible to make the scroll button do two things ie increment/decrement the date and execute your macro?

Skip
I humbly appologise, you would indeed win the wager as you didnt write that macro - you've helped me so many times over the past 3 years I just automatically thought it was you!
I tried your suggestion, but it crashed at 1 Jan

Many thanks Guys

 

[SkipVought]

What is the value of X?

If the calculation resulted in a NEGATIVE, ZERO or greater than 65536, of courst it will bomb!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

oops, I meant, if X returnd a non-existant value

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Doesn't your scroll button have some code attached to it ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shytott]

Skip
The original marco, ie:
Range("A24").Select
ActiveWindow.FreezePanes = True
x = 24 + Int(Now()) - Range("b24")
Range("b24").End(xlDown).Select
Application.Goto Reference:="R" & x & "C2"
was used to scroll the bottom section of the worksheet to show todays row, so the value of X is equal to 24 (to get you past the frozen section) plus the number of rows down to todays date (though I must admit Im not sure what the '- range ("B24") is doing).

PHV
I had thought of that, but I cant find any increment/decrement code thats attached to the scroll button - or any code for that matter.

As your change almost does the job, would it be possible to have two seperate buttons (as opposed to the scroll button) each of which goes to K19, increments it then executes your new macro? I tried to ref the cell and increment it but couldnt get the syntax correct.

Cheers

 

[PHV]

How did you create the scroll button ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shytott]

I just selected it from the Control Box Toolbar then in Properties, chnaged the max and min integers to coincide with 1 Jan - 31 Dec

 

[PHV]

Right click it in design view and click 'View code' (or similar) in the context menu.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shytott]

Yes, I can get to:

Private Sub ScrollBar1_Change()

End Sub
easily enough, and presumably your macro will sit in there, but where Im struggling is, there are two buttons making up the scroll bar, so how do I get the upper one to increment then execute the macro, and the bottom one to decrement?

What about my previous idea, how would you code:
Goto K19 and then increment the current value?

Cheers

 

[PHV]

there are two buttons making up the scroll bar
You see 2 buttons but you have a single control.
Did you try to call my GotoX sub in the Change event procedure of your control ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shytott]

PH
I wonder if we're thinking about the same button here! The scroll button Ive got (from the Tool Box menu) is a single button when you draw it on your worksheet but which has two clickable areas which increment/decrement. Hence it only shows up as a single control in VBA.
With regard to the 'Change Event' procedure, where do you access that? All I can see is right clicking the scroll button and selecting Properties? - so no, I havent yet used GotoX anywhere yet (other than on a standard macro button).
Cheers
AD

 

[PHV]

Didn't you click the 'View code' context menu ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shytott]

Hi PH

I've cracked it!!!!
I think it was probably what you were saying earlier but the penny didnt drop with me. All Ive done is to drop the GotoX sub into:
Private Sub ScrollBar1_Change()

End Sub
I just didnt think it would be that easy!

But as ever, Thanks for pointing me in the right direction Guys, I take my hat off to you, you save the day again!

Cheers
AD