Need VBA to select and reassign moving XY chart data

Need VBA to select and reassign moving XY chart data

Post by thatgu » Wed, 06 Jun 2012 11:59:56



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*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!

 
 
 

Need VBA to select and reassign moving XY chart data

Post by dguillett » Wed, 06 Jun 2012 21:50:03


This can probably be greatly simplified. Provide your excel version and the file.

Don Guillett
Microsoft MVP Excel
SalesAid Software

From: thatguy
Sent: Monday, June 04, 2012 9:59 PM

Subject: $$Excel-Macros$$ Need VBA to select and reassign moving XY chart data

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*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.

------------------------------------------------------------------------------------------------------