Help to write range(cells(),cells())

[Akbarov]

Hello I am trying to update chart source data automatically.

I need to use something like : = sh.Range(sh.Cells(3, 6 + x).Address, sh.Cells(LR, lc).Address) to make row and column dynamic.

How can I write this '='Sheet1'!$E$2:$E$57,'Sheet1'!$P$2:$Y$57'

Thanks in advance.

 

[DanteAmor]

How can I write this '='Sheet1'!$E$2:$E$57,'Sheet1'!$P$2:$Y$57'

In rng1 you have the first part and in rng2 the second part.

Try this:

Sub testrng()
  Dim sh As Worksheet
  Dim lr As Long, lc As Long
  Dim rng1 As String, rng2 As String
  
  Set sh = Sheets("Sheet1")
  
  lr = sh.Range("E" & Rows.Count).End(3).Row
  lc = sh.Cells(2, Columns.Count).End(1).Column
  rng1 = sh.Name & "!" & sh.Range("$E$2:$E$" & lr).Address
  rng2 = sh.Name & "!" & sh.Range(sh.Cells(2, lc), sh.Cells(lr, lc)).Address
  
  ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
  ActiveChart.SetSourceData Source:=Range(rng1 & "," & rng2)
End Sub

 

[Akbarov]

Thank you for your time Danta, I tryed your code. But at last line I get error Method range of object _Global failed

 

[DanteAmor]

I am only giving an example of how to obtain range1 and range2. And I applied it in the creation of a graph.

But you're not really putting how you have your data in the sheet or what you want to do.

Give an example using XL2BB to of what you have on the sheet and explain what you want to do.

 

[Akbarov]

I made some changes to use it in my sheet. Here it is:

Sub testrng()
  Dim sh As Worksheet
  Dim lr As Long, lc As Long
  Dim rng1 As String, rng2 As String
 
  Set sh = ActiveSheet
 
  'lr = sh.Range("E" & Rows.Count).End(3).Row
  'lc = sh.Cells(2, Columns.Count).End(1).Column
  lr = 57
  lc = 24
  rng1 = sh.Name & "!" & sh.Range("$E$2:$E$" & lr).Address
  rng2 = sh.Name & "!" & sh.Range(sh.Cells(2, 16), sh.Cells(lr, lc)).Address
 
  sh.ChartObjects("Cluster" & 2).Activate
  ActiveChart.SetSourceData Source:=Range(rng1 & "," & rng2), PlotBy:=xlColumns
End Sub

I also changed this part: sh.Range(sh.Cells(2, 16) thought that was a typo.
Some rows are hidden, can that cause any kind of problem?

 

[DanteAmor]

sh.ChartObjects("Cluster" & 2).Activate

That line doesn't work for me.

But if I put the following it works with your code:

sh.Shapes.AddChart2(201, xlColumnClustered).Select

What error do you get?
In which line do you have the error?

Again, you can put a sample of your data.

 

[Akbarov]

I get error in last line: ActiveChart.SetSourceData Source:=Range(rng1 & "," & rng2), PlotBy:=xlColumns
error: Method range of object _Global failed

 

[DanteAmor]

Again, you can put a sample of your data.

I can't reproduce your problem because you don't want to share an example of your data.

I just can't go further, the tests with my data and with the type of graph that I use for my example work.

Another option is to share your file on google drive.

You could upload a copy of your file to a free site such google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[Peter_SSs]

Try this

Sub testrng_v2()
  Dim sh As Worksheet
  Dim lr As Long, lc As Long
  Dim rng1 As Range, rng2 As Range
 
  Set sh = ActiveSheet
 
  'lr = sh.Range("E" & Rows.Count).End(3).Row
  'lc = sh.Cells(2, Columns.Count).End(1).Column
  lr = 57
  lc = 24
  Set rng1 = sh.Range("$E$2:$E$" & lr)
  Set rng2 = sh.Range(sh.Cells(2, 16), sh.Cells(lr, lc))
 
  sh.ChartObjects("Cluster" & 2).Activate
  ActiveChart.SetSourceData Source:=Range(rng1, rng2), PlotBy:=xlColumns
End Sub

 

[Akbarov]

You can test in any of charts in this file

formrexcel.xlsm

Shared with Dropbox

www.dropbox.com