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.

A	B	C
1	1	1.4
1	2	1.5
1	3	1.6
2	1	2.1
2	2	2.2
2	3	2.3
2	4	2.4
2	5	2.5

<tbody>
</tbody>

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

 

[Michael M]

UNTESTED, but try

Set graph_first = Range(Cells(first_item_start, 2), Cells(item_row_end, 3))

 

[JL888]

Hi Michael

Thanks for the prompt reply. I tried your line of code but it didn't work, it still returns the same error message and the value for item_row_end is still equal to 0.

 

[Michael M]

Is ws the active sheet ??
If not Try

Set graph_first = ws.Range(Cells(first_item_start, 2), Cells(item_row_end, 3))

 

[JL888]

Hi Michael
Thanks for your input. I have designated ws as Worksheets("Sheet1") where Sheet1 is the sheet which contains the data for all items (Items 1,2,3 and so on). I would want to create the chart for each item on separate sheets (better still - export them as individual files). In the case of separate sheets, I believe the active sheet would be the one where a chart is created and not the data sheet ws or "Sheet1." However, I am not sure how to correct this. Thanks

 

[Michael M]

Try stepping through the code manually, to see why your data comes up with row_end = 0
It's a bit hard to do it remotely without a block of data to play with.
If you don't get anyone else to jump in and help, upload the workbook to a host site and I'll try and step through it.

 

[Jonmo1]

According to your If Structure, the variable item_row_end will only be given a value IF next_item <> first_item

So if next_item DOES = first_item, then item_row_end doesn't have a value assigned to it, causing that error.

 

[Michael M]

Thanks for stepping in Jonmo...I don't have excel at the moment....

 

[JL888]

Thanks Jonmo and Michael, I will try fixing that line.

 

[JL888]

Hi. Ok, so I figured out that the problem was that when it looped through the values for i = 1, it did not have the item_row_end value yet to create the graphing range for the first item, it only had the start row that was fixed for the first item. It would only be after the second loop that it would acquire the item_row_end value. However, I have tried the following cleaned up code (everything is already declared) to find all the start and row addresses for each item before graphing. I then ended the while loop after all the addresses had been found and assigned. Then I would want to start a new loop to graph each item using their previously found start/end row addresses (which are variables dependent on i). However, I am not sure how to do this as I am confused as to how to bring in the i iterations again. Would anyone be able to help me here? Thanks

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
            
            c = ws.Cells(1, 1).Row
            ws.Cells(c, 5).Value = "First"
        
        ElseIf next_item <> first_item Then
                           
            If ws.Cells(i + 1, 1).Value <> "" Then
                j = ws.Cells(i + 1, 1).Row ' found start row address
                ws.Cells(j, 5).Value = "Start" 'Indicates first row address
            End If
                
            k = ws.Cells(i, 1).Row
            ws.Cells(k, 5).Value = "End" 'Indicates last row address
                       
        End If
                              
            first_item_start = c
            item_row_start = j 'Stores first row address
            item_row_end = k 'Stores last row address
           
     i = i + 1
       
    Loop