Solved

Excel - Split Screen According to date??

Posted on 2002-05-10
11
271 Views
Last Modified: 2008-02-26
In my spreadsheet I have the date in the first column.

1/1
1/2
1/3
1/4
etc.  going through the whole year.


I want to has a Split Screen with the row with the current date showing in the split screen.  If that is not the correct terminology I am sorry.

I need the row with the current date displaying at the top.

I know in javascript you can read the date from the computer but do not know Excel/VBA.

I will increase points to 300 if it can be done.

If it cannot, I still will award these points for information and ideas.
0
Comment
Question by:rtho7
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
11 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 7001986
HI rtho7,

don't know if this is waht you need but in excel you can use =today() to get the current date, and

you can split a screen from windows | split

:O)Bruintje
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7002029
well i keep reading this and now it seems to dawn.....

you want to run a piece of code and split the screen where the date() = Range("A").value?

Ok in your workbook

-Open the VB editor qith ALT+F11
-then insert a new module
-paste the code

Sub InsertSplit()
Dim i As Integer
 
  Range("A2").Select
  Selection.End(xlDown).Select
  For i = 1 To Selection.Row
    If Range("A" & i).Value = Date Then
      With ActiveWindow
        .SplitColumn = 0
        .SplitRow = i
        Exit Sub
      End With
    End If
  Next
End Sub

-now save
-choose F5 to run

this code assumes A column to hold the date values

HTH:O)Bruintje
0
 

Author Comment

by:rtho7
ID: 7002103
I am having a problem:

It goes to the end of the entries in A column.

I cut out most of the date A column except for just a few entries.  It works well with just a few entries.

I add back all the dates and it ran the macro.  It went to the end of entries in A column.

???
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 44

Expert Comment

by:bruintje
ID: 7002111
that's funny, how many dates are there?

could you send me an empty sheet with a complete date column? would be faster mulbum@worldonline.nl
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7002143
All right this is a nuce one

Sub InsertSplit()
Dim i As Integer
 
 Range("A2").Select
 Selection.End(xlDown).Select
 For i = 1 To Selection.Row
   If Range("A" & i).Value = Date Then
     Rows(i).Select
     With ActiveWindow
       .SplitColumn = 0
       .SplitRow = 17
       Exit Sub
     End With
   End If
 Next
End Sub

Excel uses relative reference for the split, and it's relative against the activewindow, so i want the split on row 17 of the active window

:O)Bruintje
0
 

Author Comment

by:rtho7
ID: 7002166
It works and I will give you the 300 pts. I do have another question pertaining to this though.

Is there a way to update the split each time/day the excel sheet is opened?

Any help will be appreciated.

0
 
LVL 44

Expert Comment

by:bruintje
ID: 7002170
we can do it in this thread, i'll patch something up
0
 
LVL 44

Accepted Solution

by:
bruintje earned 300 total points
ID: 7002184
-doubleclick on the thisworkbook icon on the left pane
-place this code in the code window

Option Explicit

Private Sub Workbook_Open()
  Call InsertSplit
End Sub

-then place this code in the module1 instead of the earlier version
-this one will first delete the current split
-then place the new one based on the current date

Option Explicit

Sub InsertSplit()
Dim i As Integer
 
  With ActiveWindow
    .SplitColumn = 0
    .SplitRow = 0
  End With
  Range("A2").Select
  Selection.End(xlDown).Select
  For i = 1 To Selection.Row
    If Range("A" & i).Value = Date Then
      Rows(i).Select
      With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 17
        Exit Sub
      End With
    End If
  Next
End Sub


HTH:O)Bruintje
0
 

Author Comment

by:rtho7
ID: 7002245
Works great.  Thank you for your effort.  I'll never be a programmer but I am learning alot.
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7002256
thanks to you i made 300K but i still got a long way to go :) tomorrow i'm going to celebrate this humble achievement

PS for this sort of tricks you don't have to be a programmer it helps a lot, but some playing around and of course lots of practice on EE will get you there

Have fun
0
 

Author Comment

by:rtho7
ID: 7002294
Glad to be part of your achievent.  Congrats and thanks for all the help.

0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Outlook Free & Paid Tools
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question