Monday, 11 April 2011

Code and Exercise




Ok so I have officially broken up for the Easter. It’s the first day off of University, I’m settling down to do work. Not the 3 essays and 3 exams I have to prepare for but a spreadsheet I need to create for the RSPCA. Yeah I get it. I am spending valuable time to prepare for university to spend time for a charity. To be honest the only reason I’m doing it is because I have NO experience in the accounting industry. So I put my keen mind to the task of working out what I was going to do about this... I came up with a solution.
The RSPCA! It’s a charity that my father works for, I gave the ol’ man a call to see if he could pull any strings. I got a phone call a couple of months later from the animal home manager here is Derby. I went in and we discussed what it is they as a branch need in terms of accounting/ financial analysis etc. From then on I have been working on something here or there for what they need. My biggest project that is still underway is the spreadsheet that allows the user to input the sales data from either the animal home or the locally run charity shop and then gives different types of analysis from it. For example; the forecast of the following weeks sales figures for different items/ days of the week. It also tells the user if they are improving over the year or if sales figures are falling. I find it all pretty basic. It is done in Excel and by no means looks good. In fact the VBA is some pretty ugly looking code that could be neatened up. Jag strunta det. It works and I by no means profess to be a programmer.
I am however stuck at one particular problem. Following a meeting with a manager she wants to be able to select x date and y date and have it automatically draw a graph, pretty easy I said. Even in terms of then extending it to let you then select multiple Items to graph them against each other. It has now been 3 months and I still haven’t done it. University has piled up to a huge scale and every time I look at the code it should work. But there is a data mismatch somewhere and I just can’t find the problem. I need to get this nailed!
Below is a copy of the code. I know there are glaring mistakes but not being a computer person I can’t see them. Please review and scream at me...
Oh op_date, end_date and Item all refer to the relevant textbox or listbox on the userform.


Private Sub btn_graph_Click()
'declare all the variants that will be needed for the graph
Dim op_date As TextBox ‘start date that the graph should be drawn from
Dim end_date As TextBox ‘the end date that the graph is drawn from
Dim Item As ListBox ‘is a list box with all the items that are required to be viewed in graph form
Dim op_target As Variant ‘target cells
Dim end_target As Variant ‘target cells
Dim item1 As Object ‘target cells
‘select the range where all the dates are kept.
Range("A2:AV2").Select
‘find the address of the cell which holds the start date
For Each op_target In Selection.Cells
If op_target = op_date Then
Find.Address
End If
Next
‘find the address of the cell which holds the end date
For Each end_target In Selection.Cells
If end_target = end_date Then
Find.Address
End If
Next
‘find the address of the cell which holds the Item
For Each item1 In Selection.Cells
If item1 = Item Then
Find.Address
End If
Next
‘draw a graph from the addresses found
Range(op_target, end_target).Select
                 Range("item1").Activate
                 ActiveSheet.Shapes.AddChart.Select
                  ActiveChart.SetSourceData Source:=Range("op_target,end_target")
                   ActiveChart.ChartType = xlLine
                 ActiveChart.Axes(xlCategory).Select
                 ActiveChart.Axes(xlCategory).ReversePlotOrder = True
                 ActiveChart.SeriesCollection(1).Trendlines.Add
                 ActiveChart.Location Where:=xlLocationAsNewSheet
    UserForm1.Hide ‘hide the user form

End Sub

Happy checking!
Oh ps. Dance work out videos... solid! Not in terms of exercise. Although I’m if you do it properly it would. But I just couldn’t keep up! It doesn’t help that I’m totally uncoordinated. But the ab work out is pretty sweet. J It is defiantly worth giving ago. If nothing else but to show how manly you are as they make you stroke your face... lol awkward!

No comments:

Post a Comment