Take the value of a cell and then use as part of a range in VBA (Excel 2007)

[Hatterfan]

Excel 2007. Moderate understanding of VBA code.



Hi all,

I am looking to make a graph that can vary its start date (history) based on a user defined cell.

I have a graph and the coding that will allow it to select the source data range from a defined start point to the end of the data - thus allowing for more data to be added.

Dim lastRow As Long
With Sheets("Debt 90-119 Days")
lastRow = .Range("B" & Rows.Count).End(xlUp).Row

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=.Range("B1:F" & lastRow)
ActiveChart.Axes(xlCategory).Select
End With

This creates a graph with source data from B1 through to F and down as far as the lowest row with data.

I would like the B1 (as the start point) to be variable and defined upon a different cell (A1).

A1 will consist of a number that matches the row I want the range to start from.

A1 = 5. Data range = B5:F (last row).

Essentially I need the range to be: B(A1):F(last row).

Can anyone help?

Thanks.

 

[archstantn]

Try the following:

Dim x As Integer
x = Range("A1").Value
ActiveSheet.Range("B" & x, "F" & lastrow).Select

Then employ the selection to the chart data source with various code

 

[Hatterfan]

Perfect.

Slotted into my existing code and works like a dream.

I knew I was on the right lines but couldn't understand how to define x essentially.

Thank you - very much appreciated.