LinkBack URL
About LinkBacks
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!
Register To Reply
Bookmarks