I have an excel sheet that automatically pulls in the new data that I want and adds it to the next available row. I have several charts that are associated with this data and right now i have to go through and select each data set individually. which is a pain. I am trying to create a few lines of code that will automatically resize the data that is grabbed. I have the data that starts on Q4 and i want it to go to U, to what ever the value of the integer is that i tell it. I know that you can use a table to select a larger area then you need and it will grow the graph with it however those cells that i would select already have a bunch of equations in them waiting for the new data to get pulled i. once the data is pulled in these equations are automatically run and the data is ready to go.
I am more familiar with the program MatLab. where you could tell it something like CS is equal to a specific cell. then in the code you could say that your range is something like RangerRick =(Q4,U(CS)). Where this would know that i am talking about cell U with the number that CS is equal to. I am just not sure how to do it in excel.
Below i have the code that i am playing with. with some comments in it to try and explain what i am doing.
Sub check()
'
' check Macro
'
'
Dim CS As Integer ‘ Defining that CS is the value that I am pulling out of cell AQ which is the indexing number i need like i talked about
Dim RangerRick As Range ‘ defining the range that I am going to make the size of the data
Sheets("EW1060").Select ‘ calling the sheet I want to start with
CS = Range("AQ3") ‘defining what CS is equal to, up to this point the code works right
ReDim RangerRick("Q4", CS) ‘ the next 4 lines are all various attempts at trying to define my dynamic array or indexing the cells this is where i really need the help
' RangerRick = $Q$3:INDEX($Q:$U,COUNTA($Q:$Q))
' RangerRick = Range("RangerRick").Resize(,)
' RangerRick = Range("Q4", Cells(21, CS))
' MsgBox (CS) ‘ just a quick check to make sure CS was the right value
ActiveSheet.ChartObjects("Chart 1").Activate ‘ selecting the chart on the sheet
ActiveChart.PlotArea.Select ‘ next 3 lines are redefining the data that the chart is going to use
Application.CutCopyMode = False
ActiveChart.SetSourceData Source:=RangerRick ‘ it should be noted that I have not made it this far to see if just using the name to define the data is sufficient
'Sheets("EW1061").Select ‘ selecting the next sheet to work on. This should grab the data on this sheet in the same location as the others and then just repeat
'ActiveSheet.ChartObjects("Chart 1").Activate
' ActiveChart.Axes(xlCategory).MinorGridlines.Select
' Application.CutCopyMode = False
'ActiveChart.SetSourceData Source:=RangerRick
End Sub
I am more familiar with the program MatLab. where you could tell it something like CS is equal to a specific cell. then in the code you could say that your range is something like RangerRick =(Q4,U(CS)). Where this would know that i am talking about cell U with the number that CS is equal to. I am just not sure how to do it in excel.
Below i have the code that i am playing with. with some comments in it to try and explain what i am doing.
Sub check()
'
' check Macro
'
'
Dim CS As Integer ‘ Defining that CS is the value that I am pulling out of cell AQ which is the indexing number i need like i talked about
Dim RangerRick As Range ‘ defining the range that I am going to make the size of the data
Sheets("EW1060").Select ‘ calling the sheet I want to start with
CS = Range("AQ3") ‘defining what CS is equal to, up to this point the code works right
ReDim RangerRick("Q4", CS) ‘ the next 4 lines are all various attempts at trying to define my dynamic array or indexing the cells this is where i really need the help
' RangerRick = $Q$3:INDEX($Q:$U,COUNTA($Q:$Q))
' RangerRick = Range("RangerRick").Resize(,)
' RangerRick = Range("Q4", Cells(21, CS))
' MsgBox (CS) ‘ just a quick check to make sure CS was the right value
ActiveSheet.ChartObjects("Chart 1").Activate ‘ selecting the chart on the sheet
ActiveChart.PlotArea.Select ‘ next 3 lines are redefining the data that the chart is going to use
Application.CutCopyMode = False
ActiveChart.SetSourceData Source:=RangerRick ‘ it should be noted that I have not made it this far to see if just using the name to define the data is sufficient
'Sheets("EW1061").Select ‘ selecting the next sheet to work on. This should grab the data on this sheet in the same location as the others and then just repeat
'ActiveSheet.ChartObjects("Chart 1").Activate
' ActiveChart.Axes(xlCategory).MinorGridlines.Select
' Application.CutCopyMode = False
'ActiveChart.SetSourceData Source:=RangerRick
End Sub
