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

Question

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

If you want help it's best to include your current code in your question, even if it's not complete/working. — Tim Williams
– Tim Williams, Commented Dec 17, 2016 at 1:04

user3598756 · Accepted Answer · 2016-12-17 09:55:42Z

1

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

answered Dec 17, 2016 at 9:55

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Stella1234 Over a year ago

Thank you so much for this code once again. I have another quick question - now I have to select an extra column of data to plot (making it 5 data series instead of 4), and am not sure how to implement this in my code. I have tried changing the .Resize(,5) to .Resize(,6) but this runs an error

user3598756 Over a year ago

.Resize(,6) is the one and it actually works for me. what error are you facing and which line is throwing it?

Nick van H. · Accepted Answer · 2016-12-17 01:15:41Z

0

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.

answered Dec 17, 2016 at 1:15

Nick van H.

3982 silver badges14 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related