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

 

[WrldIntlR]

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.

Normally that's exactly what I would do, but the spreadsheet I'm working with contains values from the entire month, I need to add up the values only for the current day. The only reason I have to do this at all is because the users have a tendency to close out of the application and re-open it randomly, so I can't just keep a running tally from when it's open.

 

[wigi]

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

Cell does not exist. Cells refers to a certain cell on a sheet.

You could have used intellisense after the With statement, when you type . it will give you a list of possible choices (methods, properties, ...) In that list, Cells will be shown.

If you use ActiveSheet, that does not work. You'd better not activate or select specific cells, but work with the sheets directly. Just as I showed in my With ... End With construct above. Much neater, faster and no flickering of the screen.

 

[Jonmo1]

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.

something like

Dim mystr As String, mylng As Long
 
mystr = "12"
mylng = CLng(mystr)

 

[wigi]

Normally that's exactly what I would do, but the spreadsheet I'm working with contains values from the entire month, I need to add up the values only for the current day.

That's what my SumIf example gave you - right?

 

[Jonmo1]

Cell does not exist. Cells refers to a certain cell on a sheet.

You could have used intellisense after the With statement, when you type . it will give you a list of possible choices (methods, properties, ...) In that list, Cells will be shown.

Also using

Option Explicit

At the top of your modules will reveal these types of errors.

 

[WrldIntlR]

Also using

Option Explicit

At the top of your modules will reveal these types of errors.

all good things to learn. In truth I've probably written about 700 lines of code in what could have been done in 300, but I'm still a n3wb (also I'm not getting paid for it).

TBHWY, I guess I don't understand the SUMIF statement. I'll have to research that on my own. I really, really appreciate all of all y'alls help on this. It's working perfectly now.