CODEWITHSUNDEEP
vbaexcelmacros
Stella1234
Stella1234

Reputation: 35

VBA code to select columns based on user defined variables in cells

I am trying to write a code for my macro in excel with VBA but I keep getting stuck on the user defined variable part. I currently have a spreadsheet with four sets of measured data that correspond to a single date.

What I am trying to achieve is:
-Have one cell for a start date, and another cell for an end date that is specified by the user; I want to macro to read these two cells and use these two dates, read the data between them, and create a line graph corresponding to these dates with the 4 data series I have.

The end goal is to be able to create a chart between whichever two dates the user wishes to.

I have figured out the macro to plot the line graph if I specify the entire column (i.e. I have put B3:F170 as the data range) however I am not sure how integrate a user defined range in the code.

Any help would be greatly appreciated. Many thanks in advance.

Edit: Here's what I have so far, sorry if its quite messy I am just starting out!

Dim chtChart As Chart
Set chtChart = Charts.Add
With chtChart
.Name = "Chart Name"
.ChartType = xlLine
.SetSourceData Source:=Sheets("Sheet1").Range("B3:F170"),_
PlotBy:=xlColumns
End With 
Dim FromDateCell As String 
Dim ToDateCell As String 
Dim DateRange As Range 
FromDateCell = Range("I13").Value
ToDateCell = Range("I14").Value
DateRange = Range(FromDateCell, ToDateCell)
Range(DateRange).Select

Upvotes: 0

Views: 1995

Answers (2)

user3598756
user3598756

Reputation: 29421

you could first set the wanted data range and then assign it as your new chart Source, like follows:

Option Explicit

Sub main()
    Dim chtChart As Chart

    Dim FromDateCell As Range, ToDateCell As Range, DataRange As Range

    With Sheets("Sheet1") '<--| reference your relavant sheet
        Set FromDateCell = .Range("B3:B170").Find(.Range("I13").Value, LookIn:=xlValues, lookat:=xlWhole) '<--| get "initial date" cell in range B3:B170 
        Set ToDateCell = .Range("B3:B170").Find(.Range("I14").Value, LookIn:=xlValues, lookat:=xlWhole) '<--| get "final date" cell in range B3:B170 
        Set DataRange = Range(FromDateCell, ToDateCell).Resize(, 5) '<--| set the "data" range as the one ranging form "initial date" to "final date" cells extended to enclose four columns to the right
    End With

    Set chtChart = Charts.Add
    With chtChart
        .Name = "Chart Name"
        .ChartType = xlLine
        .SetSourceData Source:=DataRange, PlotBy:=xlColumns
    End With
End Sub

the same code could be rewritten (i.e. refactored) to use a specific function to get the wanted range and have your main code more readable and the whole code more maintainable

like follows:

Sub main()
    Dim chtChart As Chart

    Set chtChart = Charts.Add
    With chtChart
        .Name = "Chart Name"
        .ChartType = xlLine
        .SetSourceData Source:=GetDataRange, PlotBy:=xlColumns '<--| here you use GetDataRange() function to return the wanted range
    End With
End Sub

Function GetDataRange() As Range
    Dim FromDateCell As Range, ToDateCell As Range

    With Sheets("Sheet 1")
        Set FromDateCell = .Range("B3:B170").Find(.Range("I13").Value, LookIn:=xlValues, lookat:=xlWhole)
        Set ToDateCell = .Range("B3:B170").Find(.Range("I14").Value, LookIn:=xlValues, lookat:=xlWhole)
        Set GetDataRange = .Range(FromDateCell, ToDateCell).Resize(, 5)
    End With
End Function

Upvotes: 1

Nick van H.
Nick van H.

Reputation: 388

I think you can achieve this quite simply by using named ranges. Define a named range with the formula

=OFFSET(A8:E200,0,<column>)

Enter that as the data series as input range for the graph as

=<sheet1>! <name>

Replace all values between < and > to whatever is applicable to you. See full reference at https://support.microsoft.com/en-us/kb/183446

You can also use the offset formula in combination with index/match to find the starting/end point in order to get a subset of your data.

Upvotes: 0

Related Questions