CODEWITHSUNDEEP
excelvbavariableschartsrange
Santhana Krishnan Prakash
Santhana Krishnan Prakash

Reputation: 1

Excel VBA - Having issues while Using Range Function with multiple variables in Embedded Charts

Using VBA In a sheet I'm trying to copy & paste a Embedded chart multiple times & assign different data range for each new charts. Each chart have 3 data series in y axis & 1 data series in x axis

In 29th line of the code I'm having issues to assign the variables p, q, r in the code in the place of 83, 104, 293, The code is as below

Sub CopyChart()
Dim Sht As Worksheet
Dim chrt As Chart
Dim n As Integer
Dim k As Integer
Dim p As Integer
Dim q As Integer
Dim r As Integer
Set Sht = ThisWorkbook.Sheets("Sheet2")
Set ShtBND1 = ThisWorkbook.Sheets("BND1")
Set chrt = Sht.ChartObjects("Chart 1").Chart
n = 99
k = 2
p = 83
q = 104
r = 293
chrt.ChartArea.Copy

Do Until k >= 4
Sht.Range("A1").Select
ActiveSheet.Paste
    With ActiveChart.Parent
         .Top = n    ' reposition new chart
         .Left = 180   ' reposition new chart 
         .Name = "Chart " & k
     End With
     
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData Source:=Range("BND1!$A$2:$EZ$2,BND1!$A$83:$EZ$83,BND1!$A$104:$EZ$104,BND1!$A$293:$EZ$293")

'I am having issues to assign the variables p, q, r in above code in the place of 83, 104, 293

ActiveChart.PlotBy = xlColumns
ActiveChart.PlotBy = xlRows
    
n = n + 92
k = k + 1
p = p + 1
q = q + 1
r = r + 1
Loop
End Sub

Upvotes: 0

Views: 67

Answers (1)

rotabor
rotabor

Reputation: 4708

You can concatenate strings using + operator. Use number variables directly, they will be converted to strings by the default rule.

ActiveChart.SetSourceData Source:=Range("BND1!$A$2:$EZ$2,BND1!$A$" + p + ":$EZ$" + p + ",BND1!$A$" + q + ":$EZ$" + q + ",BND1!$A$" + r + ":$EZ$" + r + "")

Upvotes: 3

Related Questions