0

I'm pretty new to VBA and having a hard time understanding why my code keeps returning the same run-time error 1004. Our assignment is to take a workbook with x amount of sheets (for each year our data is providing) that provide all the values of each stock everyday of the year and then print out the name of the ticker, yearly change, percent change, and total volume.

So far the code runs through for the first sheet but when it tries to move onto the next sheet it gives me a run-time error at the line If ws.Cells(row, 1).Value <> ws.Cells(row + 1, 1).Value Then

I looked up different ways to loop through the sheets but I still got the same thing. 

Sub testing()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

    ws.Range("I1").Value = "Ticker"
    ws.Range("J1").Value = "Yearly Change"
    ws.Range("K1").Value = "Percent Change"
    ws.Range("L1").Value = "Total Stock Volume"

    Dim ticker As String

    Dim yearly_change As Double
    yearly_change = 0

    Dim opening As Double

    Dim closing As Double

    Dim row As Double

    Dim counter As Double

    counter = 2

    For row = 2 To Rows.Count

        If ws.Cells(row, 1).Value <> ws.Cells(row - 1, 1).Value Then

            opening = ws.Cells(row, 3).Value

        End If

        If ws.Cells(row, 1).Value <> ws.Cells(row + 1, 1).Value Then

            ticker = ws.Cells(row, 1).Value

            closing = ws.Cells(row, 6).Value

            yearly_change = closing - opening

            total_stock_volume = total_stock_volume + Cells(row, 7).Value

            ws.Range("I" & counter).Value = ticker

            ws.Range("J" & counter).Value = yearly_change

            ws.Range("K" & counter).Value = yearly_change / opening

            ws.Range("K" & counter).NumberFormat = "0.00%"

            ws.Range("L" & counter).Value = total_stock_volume

            counter = counter + 1

            total_stock_volume = 0

            Else

            total_stock_volume = total_stock_volume + ws.Cells(row, 7).Value

            yearly_change = closing - opening

        End If

    Next row

Next ws

End Sub

I expected the code to run through all the sheets but it only runs the first sheet

TylerH
  • 20,799
  • 66
  • 75
  • 101
Nada Ibrahim
  • 31
  • 8
  • Your loop shouldn't be to `Rows.Count`. It will fail when you try to do `row + 1` - because that is a row that doesn't exist, `Rows.Count` is the maximum possible row. See [this question](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) for how to find the last used row, and loop to that. – BigBen Oct 31 '19 at 02:01
  • @BigBen Okay that definitely helped loop through the whole workbook but now im getting an overflow error – Nada Ibrahim Oct 31 '19 at 02:24
  • What line throws the error? – BigBen Oct 31 '19 at 02:28
  • @BigBen ws.Range("K" & counter).Value = yearly_change / opening and I'm now noticing that my last cell in my last worksheet isn't returning values for yearly change, percent change, and total stock volume – Nada Ibrahim Oct 31 '19 at 02:32
  • What are the values of `yearly_change` and `opening` when the error occurs? – BigBen Oct 31 '19 at 02:35
  • @BigBen it was 0 for opening , which is why its giving me an overflow error since we can't divide by 0. I completely didn't see that since we have thousands of datasets. Would I create an if condition that outputs zero whenever that occurs? Thank you so much for your help! – Nada Ibrahim Oct 31 '19 at 02:46
  • Just test if `opening` is `0` before attempting to divide, so yes. – BigBen Oct 31 '19 at 02:48
  • @BigBen I added an if statement for when `opening` and `closing` are 0 to just output 0 otherwise output the `yearly_change` which works so thank you! – Nada Ibrahim Oct 31 '19 at 03:21

0 Answers0