Plot in VBA Using Variable

Question

So here is the code that I am working with:

c = Application.Match("Test", Range("F1:F130"), 0)        
    ActiveChart.SeriesCollection(1).XValues = "='" & shtname & "'" &  "!$B$104:$N$104"

So similar to the plot I would want to be able to say because c+4 = some integer which is not always 104.

ActiveChart.SeriesCollection(1).XValues = "='" & shtname & "'" &  "!$B$(c+3):$N$(c+3)"

I have tried using doing something like:

ActiveChart.SeriesCollection(1).XValues = "='" & shtname & "'" & "!$B$" & " (c+3):$N$" & " (c+3) "

This did not work... obviously, but I am very new to VBA and the syntax, so any help is appreciated. Here is the function for shtname as well:

    Function shtname() As String
    shtname = ActiveSheet.Name
    End Function

try ActiveChart.SeriesCollection(1).XValues = "='" & shtname & "'!$B$" & (c+3) & ":$N$" & (c+3) — MutjayLee
– MutjayLee, Commented Jun 3, 2016 at 18:33
@MutjayLee worked like a charm, submit it as an answer and I will accept it — Adam Warner
– Adam Warner, Commented Jun 3, 2016 at 18:51

MutjayLee · Accepted Answer · 2016-06-03 18:52:45Z

1

Even variable, if it's between "" then it becomes string.

for example let's say your shtname is "Sheet1"

On your last try line

ActiveChart.SeriesCollection(1).XValues = "='" & shtname & "'" & "!$B$" & " (c+3):$N$" & " (c+3) "

Basically, you are setting XValues as

='Sheet1'!$B$(c+3):$n$(c+3)

Try

ActiveChart.SeriesCollection(1).XValues = "='" & shtname & "'!$B$" & (c+3) & ":$N$" & (c+3)

answered Jun 3, 2016 at 18:52

MutjayLee

5683 silver badges6 bronze badges

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

1 Answer 1