How to set auto-increment for range of particular rows in VBA?

Question

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 · Accepted Answer · 2018-07-24 17:22:49Z

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)))

answered Jul 24, 2018 at 17:22

Daniel Möller

86.8k24 gold badges202 silver badges222 bronze badges

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

3 Comments

BigBen Over a year ago

trim and str are superfluous - & is enough... stackoverflow.com/questions/1727699/…

Daniel Möller Over a year ago

@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.

BigBen Over a year ago

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.

Collectives™ on Stack Overflow

How to set auto-increment for range of particular rows in VBA?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related