VBA: Error Message with Range.Cell Property using Variables

[JL888]

Hi

I am using Excel 2013 on Windows 7 Professional and I am quite new to Excel VBA. I have a sample set of data arranged in the following columns:

Column A is numbered 1,2,3 etc with each number representing the identification number for an item.
Column B is numbered 1,2,3 etc (intervals)
Column C contains varied numbers (measurements of each item at each interval)

I have attached a sample of a spreadsheet which I am using to test my VBA code. This is only a simplified sample of data, the actual data which I will be accessing will have around 600-1000 measurements per item.

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]1.4[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]1.5[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[TD]1.6[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]2.1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]2.2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[TD]2.3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4[/TD]
[TD]2.4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]5[/TD]
[TD]2.5[/TD]
[/TR]
</tbody>[/TABLE]


Ultimately, what I intend to do is to create a macro which will look at the large amount of data (where the columns are in the same position) and create a graph for each item according to its identification number. In other words, I want to create a graph for item 1 with all its measurements, a graph for item 2 with all its measurements etc. I also need to export each graph into separate files somehow.

I have posted my code below, which uses a while loop and if statements. So what I have planned out in my code is:

1. Find the start row address for each item
2. Locate the end row address for each item
3. Graph each item with Column B values as the x-axis and Column C values as the y-axis.
4. Repeat for each item in the database.

However, when I run the code, I get the VBA error message “Run-time error ‘1004’, Application defined or object-defined error.” This occurs at the line:

Set graph_first = ws.Range(ws.Cells(first_item_start, 2), ws.Cells(item_row_end, 3)) 'range for the first item in data

When I check the Debug option, it states that the item_row_end variable has a value of 0. This appears to be the issue and I am not sure how to fix it.

This line of code for range is only applicable for the very first item. However, I notice that the starting row address for the first item will always be fixed while the starting row address for the other items will be varied. Thus I am also stuck on how to iterate the range so that the other graphs can be created correctly. Any help on this would be greatly appreciated too.

Thanks in advance.

Option Explicit


Sub GraphIteration()


Dim Chart As Chart
Dim item_number As Double
Dim i As Long
Dim first_item As Double
Dim next_item As Double
Dim item_row_end As Long
Dim item_row_start As Long
Dim first_item_start As Long
Dim ws As Worksheet
Dim j As Long
Dim graph_first As Range


Set ws = Worksheets("Sheet1")
i = 1
first_item_start = ws.Cells(1, 1).Row
ws.Cells(first_item_start, 5).Value = "Start" 'Indicates first row address for the very first item




'1) Find the start and end row addresses for each item
       
    Do While ws.Cells(i, 1).Value <> ""
    
    first_item = ws.Cells(i + 1, 1).Value
    next_item = ws.Cells(i, 1).Value
                    
        If next_item = first_item Then
          
            item_number = first_item
            ws.Cells(i, 4).Value = "Data: Item " & item_number
                                   
        ElseIf next_item <> first_item Then
        
           item_number = next_item
            ws.Cells(i, 4).Value = "Data: Item " & item_number
                                                
           
            If ws.Cells(i + 1, 1).Value <> "" Then
                j = ws.Cells(i + 1, 10).Row
                ws.Cells(j, 5).Value = ws.Cells(j, 1).Row
                ws.Cells(j, 5).Value = "Start" 'Indicates first row address for particular item
                item_row_start = ws.Cells(i, 5).Row 'Stores first row address for a particular item
            End If
            
            ws.Cells(i, 5).Value = ws.Cells(i, 1).Row
            ws.Cells(i, 5).Value = "End" 'Indicates last row address for particular item
            item_row_end = ws.Cells(i, 5).Row 'Stores the last row address for a particular item
                   
                                 
        End If
        
'2) Graphing Function passed Start and End Ranges
                               
                Set graph_first = ws.Range(ws.Cells(first_item_start, 2), ws.Cells(item_row_end, 3)) 'range for the first item in data
                                                                                         
                Set Chart = Charts.Add
             
                  With Chart
                                                    
                    .SetSourceData Source:=graph_first
                    .HasTitle = True
                    .ChartType = xlXYScatterSmooth
                    .Axes(xlCategory, xlPrimary).HasTitle = True
                    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Interval"
                    .Axes(xlValue, xlPrimary).HasTitle = True
                    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Diameter"
                    .ChartTitle.Text = "Data: Item " & item_number
                    .Axes(xlValue).MinimumScale = 0
                    .Axes(xlValue).MaximumScale = 3
                                        
                  End With
            
     i = i + 1
    
    Loop
            
End Sub

 

[JL888]

Hi it is me again. I am trying a new way to graph the data as the previous method proved a bit difficult. I have the following code to set a variable range based on my data. However, I get an error of "compile error: type mismatch." Would someone be able to please help me fix this? Thanks

Set graph = ("B" & item_row_start & ":" & "C" & item_row_end)

 

[JL888]

Hi. Ok I have fixed this now. However, I am still having problems with graphing. When I graph this (imagining item_row_start = 1 and item_row_end = 6) I want to create a scatter graph of 5 points from B1:C6. When I run this code I get each column's values as separate series on the one graph which is not what I want. Would someone please help me with this? Thanks.