Excel VBA simple code bug

Question

I am trying to write a function in Excel that:

Iterates through each worksheet
Checks if the supplied string exists in the supplied cell
Adds a predetermined cell value to the return value if step 2 evaluates to True

I've been stepping through my function and am getting the correct values until the function has to add the 4th cell to my return value.

Anyone have any idea what's going on? Thanks!

Function Revenue(row As Integer, col As Integer, str As String) As Integer

    Dim i As Integer

        For i = 2 To Worksheets.Count

            If Worksheets(i).Cells(row, col) = str Then

                Revenue = Revenue + Worksheets(i).Cells(21, 2) // Bug occurs on 4th iteration
                Debug.Print Revenue

            End If

        Next i


End Function

I bet it's Overflow as Revenue is an integer. Maybe someone is making too much money :-) — Sam
– Sam, Commented Nov 6, 2013 at 19:31
I'm not seeing an error, my code simply stops running and I get a #VALUE as a result. I don't think it's an overflow issue b/c it bugs out on the fourth iteration whether Revenue = 50,000 or 30,000. Is there a debug tool that I'm not using correctly? Thanks! — Christopher Jenkins
– Christopher Jenkins, Commented Nov 6, 2013 at 19:37

Gary's Student · Accepted Answer · 2013-11-06 19:24:38Z

2

You need to establish values for row and col prior to using them.

answered Nov 6, 2013 at 19:24

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sam Over a year ago

They are declared in the function signature. It's not in the code formatting block though.

Gary's Student Over a year ago

Then check the values in Cells(21,2) for each worksheet to insure they are nice numbers rather than Text.

Christopher Jenkins Over a year ago

They are always numbers, never text. I double checked.

Gary's Student Over a year ago

Then change all Integer into Double in the function header and Dims

Collectives™ on Stack Overflow

Excel VBA simple code bug

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related