0

I want to generate charts in excel. I try following code in VBA.

Sub chrt()
'
' chrt Macro
'

'
Dim i As Integer
i = 3

Do While i < 6
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("STD 8-A").Range("B1:I1,Bi:Ii")
    i = i + 1
Loop
End Sub

In that program I want to use auto-increment for Range("B1:I1,Bi:Ii") in which value of i which in loop and value of i which is in range can be change and auto increment. What is actual syntax for that. In above program loop is working but range not work proper working, and therefore program generate empty chart.

Daniel Möller
  • 84,878
  • 18
  • 192
  • 214
Madhav Nikam
  • 153
  • 2
  • 9

1 Answers1

1

While Range is taking a String, using "Bi:Ii" means literally the text "Bi:Ii".

You need to convert i (which is an Integer) to a string first and concatenate with the text:

...blablabla...).Range("B1:I1, B" & trim(str(i)) & ":I" & trim(str(i)))
Daniel Möller
  • 84,878
  • 18
  • 192
  • 214
  • 2
    `trim` and `str` are superfluous - `&` is enough... https://stackoverflow.com/questions/1727699/how-can-i-concatenate-strings-in-vba – BigBen Jul 24 '18 at 17:27
  • @BigBen, thanks for the contribution. I do this because I remember some kind of problem I had with this (maybe using an older version, I really don't rememeber). So I kept this habit.... maybe it's indeed totally unnecessary. – Daniel Möller Jul 24 '18 at 17:28
  • Not just maybe - it's totally unnecessary :) No need to `Trim` because `i` is just 3, 4, and 5. And `&` is always evaluated in a string context. – BigBen Jul 24 '18 at 17:29