Jump to content
News Ticker
  • Welcome to the Community
  • Use the forum to troubleshoot your Excel & VBA Problems
  • We will be launching a new website soon! www.ExcelWTF.com/Learn ! Join us to learn VBA from the ground up!
  • 0
Sign in to follow this  
OldFella

Automated Table Updating + Dynamic Chart

Question

Hi Caleeco

I have a couple of new issues to exercise your grey/white matter. They're not urgent or critical so if you're happy to attack them, please just work on them when the mood suits. No need for a 0300hr finish!   🙂

 

I have a worksheet with 30-odd rows of data, below which I have a table that sits alongside a chart that plots the data in that table. Column B of the table contains dates and column C contains data values taken daily from cell Y11, higher up on the worksheet. Headers are in cells B42 and C42.

 

The dates in the table show the most recent date at or near the top and the oldest dates at the bottom. As needed, I add in 5 more rows of blank cells below the headers. I actually managed to create coding to do this - to move the existing data down 5 rows and put borders around each of the 10 newly created cells below the headers.

 

I would like to improve the sheet in two ways:
a)  Lock in the Current Data
Each morning, in the cell in col C alongside today's date in col B, I set =Y11. At the end of the day I lock in that value. Next morning I move up 1 row and in col C, alongside what has now become the current date, I again set =Y11. I'd like to automate that process with coding that I can attach to a button that will:
Look for the first cell below C42 that displays a value (it will currently contain the formula =Y11);
Set that as a fixed value, not as a formula;
Move up 1 row;
If that cell is blank then set that cell =Y11, otherwise (if the table has no blank cells) Call Add5Rows and then set the blank cell (above the first displayed value below C42) =Y11;

 

There are now three sub-wishes. I actually have a number of iterations of this workbook, which is one reason I'm trying to reduce the repetitive manual data entry. In each book, one sheet handles daily results, one handles weekly results, and the other handles monthly results; the wish list and comments above referred to the daily sheet. If it's practicable to do it, after entering =Y11 in col C I'd like to move to the corresponding cell in col B and increment the value. For weekly and monthly sheets I'm guessing it's straightforward - increment weekly by 7 days and monthly by one month from the value in the cell below. Daily could be an issue given that I only record data Mon - Fri, so maybe after Calling Add5Rows the coding could add three days instead of one?  

 

That will lock the current value in the table, add in 5 new rows if the table is full, and next day's/week's/month's date will automatically display together with the new value of Y11.

 

b)  Static v Dynamic Chart
This is one we touched on a while back and I'll confess I've never been able to consistently implement.

The current chart is static; the data range is fixed and has to be updated every time I add in a new block of 5 rows. I'd love to make it dynamic so that whenever those new rows get added the chart grows like Topsy to accommodate the new data range. As an example, the chart on screen at the moment shows Chart Data Range = Summary!$B$43:$C$70. Once I Call Add5Rows it becomes Chart Data Range = Summary!$B$48:$C$75 so I have to manually change $B$48 back to $B$43. How, using Offset or whatever, do I convert this into a dynamic chart?

 

Now you can go have a well-deserved beer!!

 

And in case you want it, here's my amateur effort Add5Rows. One improvement it needs but I don't know how - how do I get rid of Range.Select    ActiveSheet.Paste? :
 

Sub Add5Rows()

Dim LastRow As Long
Dim rng As Range

    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row    'Find the last row with data in it
    End With

    Range("B43:C" & LastRow).Cut                        'Take the existing block of data and move it down 5 rows
    Range("B48").Select
    ActiveSheet.Paste
    
    Set rng = Range("B43:C47")                          'Put borders around each of the newly created blank cells
    
    With rng.Borders
        .LineStyle = xlContinuous
        .Color = vbBlack
        .Weight = xlThin
    End With

End Sub

Many thanks.

Share this post


Link to post
Share on other sites

1 answer to this question

Recommended Posts

  • 0

Hi Caleeco

If/when you get around to this one, I've simplified it a little bit. After hours of trial and error I've managed to create coding that achieves a) Lock in the Current Data, so now it's only the three sub-wishes and the dynamic charting that are still confounding me.

 

No doubt what took me hours would have taken you minutes, but at least I finally nutted it out so I'm happy.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×