[jmcgranahan]

This seems so elementary that I'm afraid to ask, but I need to get this done and I haven't been able to get it to work. I have a pretty basic code that I have been working with and basically what I am looking for is anything in a particular column in my Excel spreadsheet that has the word "Overdue" in it, I want it to calculate the sum paid for that overdue and put it in cell I1 (9th column, 1st row). The word "Overdue" is found in column I in our spreadsheet (which is the 9th column). The paid amount is found in column Q (the 17th column). I created this below and I'm not sure what I am doing wrong. I have originally had the o variable set as currency. Then I have tried it as integer, and neither one does anything. It seems like it should work. Any ideas for this elementary question?
------------------------
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 2/28/2008 by jmcgranahan
'
' Keyboard Shortcut: Ctrl+q
'
Dim o As Integer
Dim r As Long
For r = 1 To 2000
If Cells(r, 9).Value = "Overdue" Then
o = o + (Cells(r, 17))
Exit For
End If
Next
If r = 2001 Then Cells(1, 9) = o
End Sub


Jamen McGranahan

[kabia]

I don't use VBA but I do use VB6
and this is how I would do it

Dim o As Integer
Dim r As Long
For r = 1 To 2000
If lcase(trim(Cells(r, 9).text)) = lcase(trim("Overdue")) Then
o = o + (Cells(r, 17)).value
End If
Next r
Cells(1, 9) = o
End Sub

[melondotnet]

Hi, I can program in VBA...

Try replacing cells, with range.

To get the value, you use .text, but to write to a cell, use .FormulaR1C1

Note: R1C1 relate to a number-by-number referance, R = Row, C = Column