Referencing a row/cell that changes

[davidpierce]

HI all,
I have a sample attached, and in X9 I have the formula =F3+AF4-(F3-D10) which F3 is the Start Date of Project, AF4 is the position of the scroll bar and F3-D10 is the calculation of the start date and lowest date in schedule. This formula starts my calendar date at the formula calculation, =F3+AF4-(F3-D10) and row 10 is the last row in the Freeze Pane.

So, what I am wondering is there a way to reference the next row after the "Freeze Pane" or row 12 in this sample keeping in mind it will move when scrolled. So, if I scroll to row 30 (In this sample) and row 30 is directly under row 10 "the Freeze Line" then my formula would change to =F3+AF4-(F3-D30) which would move my bar chart to that day in X9 so it will follow the dates in D when scrolling down or up. What ever date in "D" directly below Row 10 is, that would be the formula.

Any ideas on this. I am not sure if I can reference this in this way.
Thanks to all,
Dave

http://www.excelforum.com/showthread...t=#post4082653

[Kaper]

to see which cell is first visible you can use:

msggbox Intersect(ActiveWindow.VisibleRange,range("D12:D100")).cells(1).Address

but to include it in the formula which will change after scroll event handler for scrolling shall be used. Probably there is one, but I never used it.

[davidpierce]

Thanks Kaper, I will continue with this then. Maybe I can find a solution.
Appreciate you taking a look at it.
Dave

[davidpierce]

Hi all,
I got this code to work for bringing up a msgbox for the row and Cell I was looking for.

Private Sub Worksheet_change(ByVal VisibleRange As Range)
MsgBox intersect(ActiveWindow.VisibleRange, Range("D12:D100")).Cells(1).Address
End Sub

So, does anyone have any ideas how I could make this change on scroll instead of change on page, and instead of a message box, put the value of D?? in a different cell like "(Q2)" for an example.

Any ideas please and thank you
Dave

[Kaper]

Hi,
The latter part is easy - in the event handle something like

Range("Q2").formula="=F3+AF4-(F3-" &  intersect(ActiveWindow.VisibleRange, Range("D12:D100")).Cells(1).Address & ")"

instead of msgbox
I used msgbox just to show that this value returns proper address.

The key problem is that scrolling event is normally not controlled by Excel. Probably such workaround could be used: http://www.cpearson.com/excel/DetectScroll.htm but I've not explored it further.

[davidpierce]

Thanks Kaper,
That worked like a charm, I will do some research today on the link and see if I can get this to work on scroll. I will let you all know if I figure it out.
Thanks

[davidpierce]

Is there a way to offset or something the rows, as per sample, I have two rows per one line of tasks. Col "D" would only be the even numbers of rows. Like" 12, 14, 16, and so on ", When I use the scroll bar or mouse to scroll with, then I get like 12, 15, 18 and when odd number of row, then I get a date of 1/0/1900
Thanks

[Kaper]

you mean in formula?
if row shall be rounded down (upwards in the sheet), for example 13->12:

Range("Q2").formula="=F3+AF4-(F3-D" & 2*int(intersect(ActiveWindow.VisibleRange, Range("D13:D100")).Cells(1).row/2) &")"

if shall be rounded up (down the screen) i.e. 13->14:

Range("Q2").formula="=F3+AF4-(F3-D" & 2*worksheetfunction.roundup(intersect(ActiveWindow.VisibleRange, Range("D13:D100")).Cells(1).row/2,0) &")"

[davidpierce]

Thanks
You are just to good at this. Wish I had your knowledge! Make mountains move for sure.
Thanks,

I have been reading up on that link, way past my experience level but working on it. I cant put on my laptop at work as don't have admin to add a dll. What a pain so have to wait till I get home today to really try it out.
Thanks
Dave

[davidpierce]

Well didn't have any luck at all with that link. I couldn't even get the .dll to register? I tried copy paste, drag and drop, and nothing, gave an error message every time I tried to run it.

If anyone has any ideas on firing the above code with the scroll, please let me know would you?
Thanks

[Leith Ross]

Hello davidpierce,

You can return the row and column easily using the following code...

    row = ActiveWindow.ScrollRow
    col = ActiveWindow.ScrollColumn

[davidpierce]

Thank you Leith Ross, and please forgive me as I am not very good at VBA. I can figure out a few things but some of these things just baffle me.

I have this code that gives me the result I need, I just want it to fire when I scroll instead of having to change by doing something in worksheet. Is there a way to fire this code while scrolling?

Private Sub worksheet_change(ByVal VisibleRange As Range)
Range("Q2").Formula = "=F3+AF4-(F3-D" & 2 * WorksheetFunction.RoundUp(intersect(ActiveWindow.VisibleRange, Range("D13:D100")).Cells(1).Row / 2, 0) & ")"
End Sub