Microsoft Excel - Mid addr formula pulliing wrong date

Asked By Rowland Hamilton on 31-Jan-12 01:46 PM

Folks:

Is this error due to using the "Addr = Mid(VisableRowsRange.Address, 4, InStr(1, VisableRowsRange.Address, ":") - 4)"?
I think somehow this mid formula is identifying the 12/2/2026 as 2/2/2005, is this correct? How can I fix that?
The topsheet is pre-populated with dates and is intended to pull the sum of amounts in the source query that fit between dates in the top sheet.

ex:
top sheet dates/Amounts
8/11/2004   $200
10/2/2004      $0
11/24/2004    $0
12/1/2004    $100
2/2/2005    $115.90

Source Query dates/Amounts
8/11/2004    $200
12/1/2004    $100
2/8/2005    $150
12/2/2005    $115.90

I can't tell how this code is choosing its ranges, but the the topsheet, amount next to 2/2/2026 should be the cummulative amount in the query between 12/2/2026 and 2/2/2026 = 0. For some reason, the macro is pulling $115.90, which is the amount in the query corresponding to 12/2/2026 $115.90. Odd because there are 23 rows with values not equal to zero between 2/2/2026 and 12/2/2026 in the query results (not listing them all in example). There is no 2/2/2026 in the query.
code snippet:

Sheet3.UsedRange.AutoFilter Field:=1, Criteria1:=Sheet1.CmbNumber.Text

    'goes to Class Modules: BExEventHandler
    Set VisableRowsRange = Sheet3.Range("A4:G1000").Cells.SpecialCells(xlCellTypeVisible)
    Addr = Mid(VisableRowsRange.Address, 4, InStr(1, VisableRowsRange.Address, ":") - 4)

    'sheet1 is "Topsheet". column 1 "DATE FROM"
    For i = 7 To 10000
      If Sheet1.Cells(i, 1) = "" Then Exit For
      SDat = DateValue(Sheet1.Cells(i, 1))

      Set StAddr = VisableRowsRange.Find(SDat, LookIn:=xlValues)
      Set EndAddr = StAddr

      Set Addr_String = StAddr

      If Not Addr_String Is Nothing Then
      'Creates additions
        Do
          Amt = Amt + StAddr.Next.Next
          Set StAddr = VisableRowsRange.FindNext(StAddr)
        Loop While Not Addr_String Is Nothing And EndAddr.Address <> StAddr.Address
        Sheet1.Cells(i, 2) = Amt

       'Goes to BExEventHandler Private functions
        Set StAddr = Nothing
        Set EndAddr = Nothing
        Amt = 0
      End If
    Next i

End code.
Let me know if you can help.

Thanks, Rowland Hamilton

P.S. BEx Event handler code:

Private Sub pAppEvents_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Call Common.SheetChange(Sh, Target)
End Sub

Public Sub SheetChange(ByVal iSheet As Worksheet, ByVal iTarget As Range)
On Error Resume Next
Call pAddin.ExcelInterface.SheetChange(iSheet, iTarget)
On Error GoTo 0
End Sub

wally eye replied to Rowland Hamilton on 31-Jan-12 04:32 PM

I'm not quite following what you are trying to do The dates in Top Sheet are entered in, you want to get a sum of the values in the source query that are between the dates? Is there a reason you aren't just using a sumif?

I don't see that Addr is being used anywhere in the procedure, I doubt that it is causing a problem. You could probably get it a bit easier like this:

Addr = visablerowsrange.Cells(1, 1).Row

Any chance you could post the spreadsheet?

Nor do I see anything in the procedure that actually modifies Sheet1 column 1, which is where the topsheet 2/2/2026 date is.

lol, I stepped through it and when it got to the .Find it is looking at the values. It "Finds" "2/2/2026" in "12/2/2026" as a text field. If you change it to look in xlFormulas instead of xlValues you will get around this. I think the xlValues is very literal, last week someone was wanting to find numbers too large for their cells showing up as #'s, the xlValues found them.

There should be a much better way to do what you are trying to do, I think even a sumif or sumifs would work.

Rowland Hamilton replied to wally eye on 31-Jan-12 08:05 PM

Can't sumif b/c there are dates in the topsheet that do not appear in the query, but each date in topsheet needs the cumulative total since the last date in topsheet.

wally eye replied to Rowland Hamilton on 31-Jan-12 10:16 PM

For top sheet B2:

=SUMIFS(Sheet3!$B$2:$B$5,Sheet3!$A$2:$A$5,"<="&$A2)

and B3 down:

=SUMIFS(Sheet3!$B$2:$B$5,Sheet3!$A$2:$A$5,"<="&$A3,Sheet3!$A$2:$A$5,">"&$A2)

Otherwise, a different bit of VBA:

Public Sub btnSum_Click()

Call SumDateRanges(Worksheets("Sheet1").Range("A2:B2"), Worksheets("Sheet3").Range("A2:B2"))

End Sub

Public Sub SumDateRanges(ByVal rngTotal As Range, ByVal rngData As Range)

Dim arrTotal As Variant

Dim arrData As Variant

Dim lngLastRow As Long

Dim intCol As Integer

Dim lngTotalRow As Long

Dim lngDataRow As Long

Dim datLast As Date

Dim lngCurrDataRow As Long

intCol = rngTotal.Cells(1, 1).Column

lngLastRow = rngTotal.Parent.Columns(intCol).Find(What:="*", After:=Cells(1, intCol), _

SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

arrTotal = rngTotal.Resize(lngLastRow - rngTotal.Row + 1, rngTotal.Columns.Count)

intCol = rngData.Cells(1, 1).Column

lngLastRow = rngData.Parent.Columns(intCol).Find(What:="*", After:=Cells(1, intCol), _

SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

arrData = rngData.Resize(lngLastRow - rngData.Row + 1, rngData.Columns.Count)

datLast = 0

For lngTotalRow = LBound(arrTotal) To UBound(arrTotal)

arrTotal(lngTotalRow, 2) = 0

For lngDataRow = LBound(arrData) To UBound(arrData)

If arrData(lngDataRow, 1) > datLast _

And arrData(lngDataRow, 1) <= arrTotal(lngTotalRow, 1) Then

arrTotal(lngTotalRow, 2) = arrTotal(lngTotalRow, 2) + arrData(lngDataRow, 2)

End If

Next lngDataRow

datLast = arrTotal(lngTotalRow, 1)

Next lngTotalRow

rngTotal.Resize(UBound(arrTotal), UBound(arrTotal, 2)) = arrTotal

Set rngData = Nothing

Set rngTotal = Nothing

End Sub

Rowland Hamilton replied to wally eye on 01-Feb-12 09:15 PM

Couldn't get it to work. Can you post your file so I can see it work? - Thank you, Rowland

wally eye replied to Rowland Hamilton on 02-Feb-12 05:22 PM

Here you go, both version on the sheet, formulas in Sheet1 and VBA in Sheet2, both pointed to data in Sheet3:

UpdateTotals.zip