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

 

[dee101]

Leith, Cut down version of the file is at http://www.filedropper.com/checkbook
All macros have been removed except yours.
Thanks

 

[Leith Ross]

Hello Dee101,

I downloaded the workbook successfully. Honestly, I am confused by the problem. It should work but isn't. I will figure it out or a work around.

Sincerely,
Leith Ross

 

[Leith Ross]

Hello Dee101,

Okay, since I couldn't get to work the way I wanted originally, here is the work around that does work.

'Thread: http://www.mrexcel.com/forum/showthread.php?p=2783356&posted=1#post2783356
'Poster: Dee101
Sub BalanceInfo()

  Dim Cell As Range
  Dim HiBal As Double
  Dim hiDate As Date
  Dim LastRow As Long
  Dim LoBal As Double
  Dim loDate As Date
  Dim Rng As Range
  
   'gets the last row with data
    LastRow = Sheet1.Cells(Rows.Count, "C").End(xlUp).Row
    
   'Define the balance range
    Set Rng = Sheet1.Range("H4:H" & LastRow)
    
   'Set loBal to highest Double value initially
    LoBal = 1.7976931348623E+308
    
     'Get low and high balances
      For Each Cell In Rng
        If Cell < LoBal Then LoBal = Cell: loDate = Cell.Offset(0, -5)
        If Cell > HiBal Then HiBal = Cell: hiDate = Cell.Offset(0, -5)
      Next Cell
       
   MsgBox "The lowest balance was " & Format(LoBal, "#,##0.00") & " on " & loDate _
        & vbNewLine & vbNewLine _
        & "The highest balance was " & Format(HiBal, "#,##0.00") & " on " & hiDate _
        , , "Balance Information"

End Sub

Sincerely,
Leith Ross

 

[dee101]

Got a error when I ran it, saw where the code had sheet1, changed it to sheet2 and works fine.
Thanks

 

[Leith Ross]

Hello Dee101,

Oooh! I knew I forgot to change something back. Good catch. Glad it is working for you.

Sincerely,
Leith Ross