-1

I'm trying to cycle through all the sparklines (500+) in an excel spreadsheet.

I then need to set the vertical range to the value in a cell to the left of each sparkline. e.g. Cell A1 has the value '5'. Cell A2 has the sparkline in it. I need the vertical range of the sparkline set to 5.

The sparklines are ungrouped (ie one sparkline in each sparkline group)

I've got as far as the code below but I can't seem to figure out how to refer to the location of the sparkline, and then offset it.

Any help greatly appreciated!

Dim i As Integer
Dim SpLG As SparklineGroup
Dim SpLG_ref As String
Dim SpLG_range as Range

For i = 1 To Cells.SparklineGroups.Count
    Set SpLG = Cells.SparklineGroups(i)

    SpLG_ref = SpLG.Location.Address
    SpLG_range2 = Range("SpLG_range").Offset(0, -1)    << ERROR

    SpLG.Axes.Vertical.MaxScaleType = xlSparkScaleCustom
    SpLG.Axes.Vertical.CustomMaxScaleValue = SpLG_range2.Value

Next i
Mark
  • 1
  • What is the error you are getting? (You haven't declared SpLG_range2.) – Andy G Jan 22 '18 at 16:32
  • With Offset you are attempting to look one column to the left, and there is no column to the left of A. – Andy G Jan 22 '18 at 16:34
  • 1
    Specify `Option Explicit` at the top of the module, then Debug > Compile and fix your code until it compiles, then update your snippet. You're referencing undeclared variables, declaring unused ones, ... – Mathieu Guindon Jan 22 '18 at 16:34
  • 1
    "Cell A2 has the sparkline in it." - Do you mean B1 has it? – CLR Jan 22 '18 at 16:42
  • `SpLG_range2` is clearly to hold a range, not an address @Xabier. If Mark follows @Mat's Mug suggestion, he'll see what the problem is. – CLR Jan 22 '18 at 16:45
  • How about replacing your error line with `SpLG_range2 = SpLG.Location.Offset(-1, 0).Value` – Xabier Jan 22 '18 at 16:48

2 Answers2

1

You need to Set SpLG_range2, rather than just assign it that way. But there are other issues in your code and a lot of stuff that just doesn't need to be there.

Replacing all that code with this will do what you want:

Dim SpLG As SparklineGroup

For Each SpLG In Cells.SparklineGroups
    With SpLG
        .Axes.Vertical.MaxScaleType = xlSparkScaleCustom
        .Axes.Vertical.CustomMaxScaleValue = .Location.Offset(0, -1).Value
    End With
Next
CLR
  • 11,284
  • 1
  • 11
  • 29
  • Thanks this is really helpful! Unfortunately I get a 'Type mismatch' runtime error at the line `.Axes.Vertical.CustomMaxScaleValue = .Location.Offset(0, -1).Value` – Mark Jan 23 '18 at 15:05
  • If you get the error pop up again, click debug and then enter `?SpLG.Location.Address` into your Immediate Window (in the VBE) to get the location of the Sparkline that is erroring. Check that it has a valid value in the cell to the left of it. – CLR Jan 23 '18 at 16:29
  • Yes all the graphs are in column K, and the values are all immediately to the left in column J (they are integers). I've checked that there are no blank or missing numbers, but still get the error. Do you have an example spreadsheet where this works? I'm using Excel 2010 64-bit if that makes a difference? – Mark Jan 31 '18 at 12:50
  • Okay, so I think I understand the problem now (I was able to reproduce it) but I'm not sure I know how to fix it. The issue is where the `SparklineGroup` is referring to a bunch of Sparklines (hence group). If it's just one, then the `.Location` is referring to a single cell, which you can Offset from to get a single value. However, if there is a group of Sparklines, then it returns the *`Range`* that contains the lines, which you can't then take as a single value. If you try to view each single Sparkline in the group, there is no option to change the `MaxScaleType` or `CustomMaxScaleValue` – CLR Jan 31 '18 at 16:27
  • You could ***cheat*** by taking the Value from the 1st cell using: `.Axes.Vertical.CustomMaxScaleValue = .Location.Cells(1,1).Offset(0, -1).Value` but I'm not 100% sure that's what you want. The only way to get exactly what (I think) you want is to create every Sparkline separately. – CLR Jan 31 '18 at 16:29
0

Object variables must be assigned with the Set keyword:

Set SpLG_Range2 = ...

Hope this helps!

iSpain17
  • 2,502
  • 3
  • 17
  • 26