Need VBA to select and reassign moving XY chart data

[thatguy14]

I have data that I am importing regularly and the number of lines of the data is highly variable. I have written code that first inserts a column of data I need for my Y axis, then the second part of my code should select the my X and Y range and change the graph accordingly then thirdly modify the limits of the major axis to the nearest largest multiple of 10 of the data. My sections 2 and three are not working properly. If you can help with any part I would be very grateful.

Here is my code:
Sub Tip_Elevation()
'
' Tip_Elevation Macro
' Insert Tip Elevation Depth (Ft)
'
' Keyboard Shortcut: Ctrl+Shift+I

'Insert column needed for Y Axis
Cells.find(What:="Test").Activate
Selection.Offset(0, -1).Select
ActiveCell.FormulaR1C1 = "TIP"
Down
ActiveCell.FormulaR1C1 = "Elevation"
Down
ActiveCell.FormulaR1C1 = "Depth"
Down
ActiveCell.FormulaR1C1 = "(ft)"
Down
ActiveCell.FormulaR1C1 = "'-----"
Down
ActiveCell.FormulaR1C1 = "=-RC[1]"
Down
Selection.Offset(0, 1).Activate
5
If ActiveCell > 0 Then GoTo 10
GoTo 15
10
Selection.Offset(0, -1).Select
ActiveCell.FormulaR1C1 = "=-RC[1]"
Down
Selection.Offset(0, 1).Activate
GoTo 5
15
' modify_graph Macro
'
'Determine the number of rows are in data
n = 0 'number of rows in graph data
Cells.find(What:="Test").Activate 'Find Column with Test
Selection.Offset(5, 0).Select 'Select fist number of column

20
If ActiveCell > 0 Then GoTo 25 'If number exists go to 15
GoTo 30 'End counter

25
n = n + 1 'Add counter
Down
GoTo 20 'Continue counter

30 'Determine Y Vaule Range
Dim RngYVal As Range
'Cells.FindNext(After:=ActiveCell).Activate
'Cells.FindNext(After:=ActiveCell).Activate
'Cells.find(What:="Tip").Activate
Cells.find(What:="TIP", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
True, SearchFormat:=False).Activate
Selection.Offset(5, 0).Select
Set RngYVal = Range(ActiveCell, ActiveCell.Offset(n, 0))

40 'Determine X Value Range
Dim RngXVal As Range
Cells.find(What:="TIP", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
True, SearchFormat:=False).Activate
Selection.Offset(5, 6).Select
Set RngXVal = Range(ActiveCell, ActiveCell.Offset(n, 0))

50 'Set graph Data
Sheets("Curve").Select
ActiveChart.SeriesCollection(1).XValues = RngXVal
ActiveChart.SeriesCollection(1).Values = RngYVal

60 'Modify Axis Limits
61 'Find Max Depth - factor of 10
Dim Depth As Integer
ActiveSheet.Previous.Select 'Selects the Previous Sheet
Cells.find(What:="TIP", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
True, SearchFormat:=False).Activate
Selection.Offset(n + 5, 0).Select
Depth = ActiveCell
Depth = Application.RoundUp(lngRHDataRows / 10, 0)
Depth = Depth * 10
ActiveCell.Offset(1, 0) = Depth
62 'Find Max Load - factor of 10
Dim Load As Integer
Selection.Offset(-1, 6).Select
Load = ActiveCell
Load = Application.RoundUp(lngRHDataRows / 10, 0)
Load = Depth * 10
ActiveCell.Offset(1, 0) = Load
65 'Change Graph Axis limits
Sheets("Curve").Select
ActiveSheet.ChartObjects("Chart 7").Activate
ActiveChart.Axes(xlValue).Select
ActiveChart.PlotArea.Select
ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlValue).MinimumScale = Depth
ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlCategory).MaximumScale = Load
End Sub


Thank you for all and any help!

 

[thatguy14]

I have tweeked my code slightly to:

Sub Tip_Elevation()
'
' Tip_Elevation Macro
' Insert Tip Elevation Depth (Ft)
'
' Keyboard Shortcut: Ctrl+Shift+I
'
Cells.Find(What:="Test").Activate
Selection.Offset(0, -1).Select
ActiveCell.FormulaR1C1 = "TIP"
Down
ActiveCell.FormulaR1C1 = "Elevation"
Down
ActiveCell.FormulaR1C1 = "Depth"
Down
ActiveCell.FormulaR1C1 = "(ft)"
Down
ActiveCell.FormulaR1C1 = "'-----"
Down
ActiveCell.FormulaR1C1 = "=-RC[1]"
Down
Selection.Offset(0, 1).Activate
5
If ActiveCell > 0 Then GoTo 10
GoTo 15
10
Selection.Offset(0, -1).Select
ActiveCell.FormulaR1C1 = "=-RC[1]"
Down
Selection.Offset(0, 1).Activate
GoTo 5
15
' modify_graph Macro
'
'Determine the number of rows are in data
n = 0 'number of rows in graph data
Cells.Find(What:="Test").Activate 'Find Column with Test
Selection.Offset(5, 0).Select 'Select fist number of column

20
If ActiveCell > 0 Then GoTo 25 'If number exists go to 15
GoTo 30 'End counter

25
n = n + 1 'Add counter
Down
GoTo 20 'Continue counter

30 'Determine Y Vaule Range
Dim RngYVal As Range
'Cells.FindNext(After:=ActiveCell).Activate
'Cells.FindNext(After:=ActiveCell).Activate
'Cells.find(What:="Tip").Activate
Cells.Find(What:="TIP", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
True, SearchFormat:=False).Activate
Selection.Offset(5, 0).Select
'Set RngYVal = Range(ActiveCell, ActiveCell.Offset(n, 0))
Range(ActiveCell, ActiveCell.Offset(n - 1, 0)).Select
Set RngYVal = Selection

40 'Determine X Value Range
Dim RngXVal As Range
Cells.Find(What:="TIP", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
True, SearchFormat:=False).Activate
Selection.Offset(5, 6).Select
'Set RngXVal = Range(ActiveCell, ActiveCell.Offset(n, 0))
Range(ActiveCell, ActiveCell.Offset(n - 1, 0)).Select
Set RngXVal = Selection

50 'Set graph Data
Sheets("Curve").Select
ActiveChart.SeriesCollection(1).XValues = Range("RngXVal")
ActiveChart.SeriesCollection(1).Values = RngYVal

60 'Modify Axis Limits
61 'Find Max Depth - factor of 10
Dim Depth As Integer
ActiveSheet.Previous.Select 'Selects the Previous Sheet
Cells.Find(What:="TIP", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
True, SearchFormat:=False).Activate
Selection.Offset(n + 4, 0).Select
Depth = ActiveCell
Depth = Round(Depth / 10) * 10
'ActiveCell.Offset(1, 0) = Depth 'Check value
62 'Find Max Load - factor of 10
Dim Load
Dim Load2
Selection.Offset(0, 6).Select
Load = ActiveCell
Load = Round(Load / 10) * 10
'ActiveCell.Offset(1, 0) = Load 'Check value
65 'Change Graph Axis limits
Sheets("Curve").Select
ActiveSheet.ChartObjects("Chart 7").Activate
'ActiveSheet.ChartObjects("Chart 7").Axes(xlvalue,xlsecondary)
'ActiveChart.HasAxis(xlValue) = True
ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlValue).MinimumScale = Depth
'ActiveChart.HasAxis(xlValue) = True
ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlCategory).MaximumScale = Load
End Sub

Now I am getting an error at the blue line: run time error 1004 the specified dimension is not valid for the current chart type. Also, my attempts to change to x and y axis to a variable have been futile.

I do not see how to attach the spreadsheet...