NeverQuit101
07-19-2013, 07:06 AM
I am very new to VBA having only been using it for about a week with my only background in coding coming from a course on Java. So please forgive me if this code is absolutely terrible and makes your eyes bleed.
Anyway, I have a sheet and I am focusing on 4 main columns. I happen to be looking at an insurance policy inventory and the first 2 columns are plan code and duration. So for each plan code, there are several sets of durations that increment from, say, 1 to 99 until starting over. The other two columns contain data that I wish to put in a graph.
Sub Graph_Create()
'
' Graph_Create Macro
'
'
Dim Plan As String
Dim Duration As Double
Dim TempDuration As Double
Dim Count As Double
Dim TempCount As Double
Dim X As Double
Count = 2
Plan = Range("B" & Count).Value
Do While Plan <> (" ")
Sheets("Access Data").Select
Duration = Range("F" & Count).Value
TempDuration = Duration
TempCount = Count
X = 0
Do While TempDuration = Duration + X
TempCount = TempCount + 1
TempDuration = Range("F" & TempCount).Value
X = X + 1
Loop
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("Access Data").Range("'F' & Count:'F' & (TempCount - 1),'H' & Count: 'H' & (TempCount - 1)")
Count = TempCount
Plan = Range("B" & Count).Value
Loop
End Sub
So the idea here is that while it is in a certain plan code, it cycles through the durations using Count as a place holder and TempCount to increment until it reaches a new duration set. Because durations increment by 1, a new duration set will be when the new duration (TempDuration) does not equal my duration place holder (Duration) plus the number of cycles through the duration loop (X). This indicates the duration count is restarting.
I then want it to use Count and TempCount as markers for the beginning and end of data in the two colums that I wish to put in a chart on a new page. Now my main problem seems to be coming from the SetSourceData line. I have been messing around with it and I keep getting Application Defined or Object Defined Error. And in some of my attempts to change it I get Method Range of object Global failed.
I am sure I have more problems than this though and any help is greatly appreciated. Again, sorry if this code is really terrible, and sorry if this post is in the wrong place.
EDIT: I also realized that I am really only using 3 columns of data, not 4 like I said, because one of the columns I want in the graph is duration which I am also sorting through by
Anyway, I have a sheet and I am focusing on 4 main columns. I happen to be looking at an insurance policy inventory and the first 2 columns are plan code and duration. So for each plan code, there are several sets of durations that increment from, say, 1 to 99 until starting over. The other two columns contain data that I wish to put in a graph.
Sub Graph_Create()
'
' Graph_Create Macro
'
'
Dim Plan As String
Dim Duration As Double
Dim TempDuration As Double
Dim Count As Double
Dim TempCount As Double
Dim X As Double
Count = 2
Plan = Range("B" & Count).Value
Do While Plan <> (" ")
Sheets("Access Data").Select
Duration = Range("F" & Count).Value
TempDuration = Duration
TempCount = Count
X = 0
Do While TempDuration = Duration + X
TempCount = TempCount + 1
TempDuration = Range("F" & TempCount).Value
X = X + 1
Loop
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("Access Data").Range("'F' & Count:'F' & (TempCount - 1),'H' & Count: 'H' & (TempCount - 1)")
Count = TempCount
Plan = Range("B" & Count).Value
Loop
End Sub
So the idea here is that while it is in a certain plan code, it cycles through the durations using Count as a place holder and TempCount to increment until it reaches a new duration set. Because durations increment by 1, a new duration set will be when the new duration (TempDuration) does not equal my duration place holder (Duration) plus the number of cycles through the duration loop (X). This indicates the duration count is restarting.
I then want it to use Count and TempCount as markers for the beginning and end of data in the two colums that I wish to put in a chart on a new page. Now my main problem seems to be coming from the SetSourceData line. I have been messing around with it and I keep getting Application Defined or Object Defined Error. And in some of my attempts to change it I get Method Range of object Global failed.
I am sure I have more problems than this though and any help is greatly appreciated. Again, sorry if this code is really terrible, and sorry if this post is in the wrong place.
EDIT: I also realized that I am really only using 3 columns of data, not 4 like I said, because one of the columns I want in the graph is duration which I am also sorting through by