This For loop REFUSES TO WORK AND It'S ANNOYING PLEASE HELP ME

[WrldIntlR]

Hello all,

So I have a userform that tracks credits throughout a month. I need to keep a running tally of the credits given on the current day. This is what I have. It runs through the loop once, and quits. It's very annoying. Please for your assistance I will be grateful.

Function CalcDlyTtl() As Long
 
Dim i As Long
Dim l As Long
Dim iRow As Long
 
Workbooks(FileNameIs).Activate
 
ActiveWorkbook.Worksheets("OCCData").Activate
 
l = 0
 
iRow = ActiveSheet.Cells(Rows.Count, 1) _
  .End(xlUp).Row
 
With ActiveSheet
 
For i = 1 To iRow
    MsgBox (.Cell(i, 6).Value)
    If Format(.Cell(i, 8).Value, "MM-DD") = Format(Now, "MM-DD") _
    Then
        l = l + .Cell(i, 6).Value
    End If
Next
End With
 
ThisWorkbook.Activate
 
CalcDlyTtl = l
 
End Function

 

[Jonmo1]

What is the value of irow after this line executes

iRow = ActiveSheet.Cells(Rows.Count, 1) _
.End(xlUp).Row

Step through the code with F8, and after that line executes, hover your mouse over the irow variable, what is it's value?

 

[wigi]

What is .Cell in .Cell(i, 8).Value? (occurs a number of times)

 

[WrldIntlR]

What is the value of irow after this line executes

iRow = ActiveSheet.Cells(Rows.Count, 1) _
.End(xlUp).Row

Step through the code with F8, and after that line executes, hover your mouse over the irow variable, what is it's value?

It's 3.

 

[wigi]

Instead of all that code, why not simply:

Function CalcDlyTtl() As Long

    With Workbooks(FileNameIs).Worksheets("OCCData")
        With .Range("A1", Range("A" & .Rows.Count).End(xlUp))
            CalcDlyTtl = WorksheetFunction.SumIf(.Offset(, 7), Date, .Offset(, 5))
        End With
    End With

End Function

Try to avoid loops in Excel and even more, use builtin functionality like the SumIf function.

 

[Jonmo1]

MsgBox (.Cell(i, 6).Value)
should be
MsgBox (.Cells(i, 6).Value)

You have .Cell repeated 3 times, change all 3 to Cells

 

[WrldIntlR]

OK, so after stepping through the code, when I get to this the iRow value that it returns is correct value, but the function just quits entirely and returns 0 at the first IF statement. Just dumps out entirely.

The value is either going to empty, or a date in standard Now format.

FRUSTRATING.

 

[WrldIntlR]

OH MAN I hope that's it.

 

[WrldIntlR]

MsgBox (.Cell(i, 6).Value)
should be
MsgBox (.Cells(i, 6).Value)

You have .Cell repeated 3 times, change all 3 to Cells

jesus mary and joseph that was it.

Now if you know a way to convert a string to a long I'll love long time.

 

[WrldIntlR]

jesus mary and joseph that was it.

Now if you know a way to convert a string to a long I'll love long time.

And just for my edification, what is the difference??