0

I am trying to create a bar chart using data in separate columns, and am using a union of the two ranges (one range is titles, other is data) to create my graph. However, I am receiving multiple "Object variable or with block not set" error messages .

I believe this is due to an error in reading my union, as the function can create a graph using only one data set with no issues Here is what I have.

Sub Barchart()

Dim ws As Worksheet
Set ws = Worksheets("AvgValues")

Dim bchart
Dim rng As Range
rng = Union(ws.Range("D4:D10"), ws.Range("B5:B10"))

If WorksheetFunction.CountA(Range("D5:D10")) <> 0 Then
    On Error Resume Next
    ws.ChartObjects.Delete
    Set bchart = ws.ChartObjects.Add(Left:=450, Width:=350, Top:=10, Height:=270)
    bchart.Chart.SetSourceData Source:=rng
    bchart.Chart.ChartType = xlBar
    bchart.Chart.ApplyDataLabels
Else
    MsgBox ("Chart requires at least one element to be built")
End If

End Sub

Uncommenting the 'On error resume' message creates a blank graph. I assume due to the inability to read the unioned data properly.

Any help or clarification will be appreciated!

User123
  • 11
  • 2
  • 1
    When you set the `rng`, specify the worksheet for the `Range` objects you are using, and once that is fixed, you can just use: `Source:=rng` I also recommend using `Dim rng As Range` – braX Dec 03 '21 at 22:33
  • To elaborate on @braX's comment, you'll want to save the Worksheet Object like `Set ws = Worksheets("AvgValues")` and your union will be `rng = Union(ws.Range("D4:D10"), ws.Range("B5:B10"))`. After doing this, you can also replace every instance of `sheets("AvgValues").` with `ws.` – Toddleson Dec 03 '21 at 22:52
  • Ive implemented the changes you've recommended, yet I am still receiving an "Object variable or with bloc not set" message. The system is still highlighting the unioned message atm. I will update my question to include your code aswell. – User123 Dec 03 '21 at 23:25
  • https://stackoverflow.com/questions/59330121/vba-chart-macro-range-using-union-error – braX Dec 03 '21 at 23:33
  • try `set rng = Union(ws.Range("D4:D10"), ws.Range("B5:B10"))` – Wesley Dec 05 '21 at 19:58

0 Answers0