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! 

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com

To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com

Reply via email to