Help with code

[dee101]

Sub BalanceInfo()
    'gets the last row with data
    LastRow = Sheet2.Cells(Rows.Count, "C").End(xlUp).Row

MsgBox "The lowest balance was " & Format(WorksheetFunction.Min(Sheet2.Range("H4:H" & LastRow & "")), "#,##0.00") & _
vbNewLine & vbNewLine & _
"The highest balance was " & Format(WorksheetFunction.Max(Sheet2.Range("H4:H" & LastRow & "")), "#,##0.00") _
, , "Balance Information"

End Sub

I am using the code above to get the lowest and highest amounts in column H, this is working fine, I would also like to get the date in column C that corresponds to the min and max in column H. so if the min in column H was in row 24 I want the date that is in C24 something like below. Thanks using excel 2003

Sub BalanceInfo()
    'gets the last row with data
    LastRow = Sheet2.Cells(Rows.Count, "C").End(xlUp).Row
MsgBox "The lowest balance was " & Format(WorksheetFunction.Min(Sheet2.Range("H4:H" & LastRow & "")), "#,##0.00") & " on what every date it was" & _
vbNewLine & vbNewLine & _
"The highest balance was " & Format(WorksheetFunction.Max(Sheet2.Range("H4:H" & LastRow & "")), "#,##0.00") & " on what every date it was" _
, , "Balance Information"

End Sub

 

[Leith Ross]

Hello Dee101,

Try this revised version of your macro.

Sub BalanceInfo()

  Dim balHigh As Double
  Dim balLow As Double
  Dim DateRng As Range
  Dim hiDate As Range
  Dim LastRow As Long
  Dim loDate As Range
  Dim Rng As Range
  
   'gets the last row with data
    LastRow = Sheet2.Cells(Rows.Count, "C").End(xlUp).Row
    
   'Define the balance range
    Set Rng = Sheet2.Range("H4:H" & LastRow)
    
     'Get low and high balances
      balLow = WorksheetFomula.Min(Rng)
      balHigh = WorksheetFunction.Max(Rng)
    
     'Find corresponding dates in column "C"
      Set DateRng = Rng.Offset(-5, 0)
      Set hiDate = Rng.Find(balDateHigh, , xlValues, xlWhole, xlByRows, xlNext, False)
      Set loDate = Rng.Find(balDateLow, , xlValues, xlWhole, xlByRows, xlNext, False)
       
   MsgBox "The lowest balance was " & Format(balLow, "#,##0.00") & " on " & loDate _
        & vbNewLine & vbNewLine _
        & "The highest balance was " & Format(balHi, "#,##0.00") & " on " & hiDate _
        , , "Balance Information"

End Sub

Sincerely,
Leith Ross

 

[dee101]

Leith thanks for looking at this, got and error on balLow = WorksheetFomula.Min(Rng)
so I changed it to balLow = WorksheetFunction.Min(Rng)

now I am getting and error "run time error 1004 application defined or object defined error" with this line highlighted
Set DateRng = Rng.Offset(-5, 0)
???

 

[Leith Ross]

Hello Dee101,

Sorry about that. I made another type. The offset should be...

    Set DateRng = Rng.Offset(0, -5)

Sincerely,
Leith Ross

 

[dee101]

Now I get "run time error 91 object varibble or with block wariable not set"
With this line highlighted
MsgBox "The lowest balance was " & Format(balLow, "#,##0.00") & " on " & loDate _
& vbNewLine & vbNewLine _
& "The highest balance was " & Format(balHi, "#,##0.00") & " on " & hiDate _
, , "Balance Information"

If I hover over loDate and hiDate it is =nothing

 

[Leith Ross]

Hello Dee101,

I have tested this version of the macro and all the bugs should be out of it. Try this and let me know.

Sub BalanceInfo()

  Dim balHigh As Double
  Dim balLow As Double
  Dim DateRng As Range
  Dim hiDate As Date
  Dim LastRow As Long
  Dim loDate As Date
  Dim Rng As Range
  
   'gets the last row with data
    LastRow = Sheet2.Cells(Rows.Count, "C").End(xlUp).Row
    
   'Define the balance range
    Set Rng = Sheet2.Range("H4:H" & LastRow)
    
     'Get low and high balances
      balLow = Val(WorksheetFunction.Min(Rng))
      balHigh = Val(WorksheetFunction.Max(Rng))
    
     'Find corresponding dates in column "C"
      Set DateRng = Rng.Offset(0, -5)
      hiDate = Rng.Find(balHigh, , xlValues, xlWhole, xlByRows, xlNext, False)
      loDate = Rng.Find(balLow, , xlValues, xlWhole, xlByRows, xlNext, False)
       
   MsgBox "The lowest balance was " & Format(balLow, "#,##0.00") & " on " & loDate _
        & vbNewLine & vbNewLine _
        & "The highest balance was " & Format(balHigh, "#,##0.00") & " on " & hiDate _
        , , "Balance Information"

End Sub

Sincerely,
Leith Ross

 

[dee101]

I am still getting a "run time error 91 object varibble or with block wariable not set"
now with this line highlighted

hiDate = Rng.Find(balHigh, , xlValues, xlWhole, xlByRows, xlNext, False)

 

[Leith Ross]

Hello Dee101,

There error means the column has no date.

 

[dee101]

column C row 4:35, last row, all have dates in them, I checked them all using =ISNUMBER(C4), copied down, and they all came back TRUE.
Any other thoughts?

 

[Leith Ross]

Hello Dee101,

Evidently your data and my test data do not match. Can you post the data so I can compare it with mine? Or If is easier you could post the workbook on a public file sharing site like mediafire.