I have the following code to find the maximum and minimum time for a person and display the results. The problem is when l run the code its giving me results for everyone not a single person l want
Sub Saving()
Dim personname As String
Dim finalrow As Integer
Dim i As Integer
Dim newSheet As Worksheet
Dim yourWorksheet As Worksheet
Dim searchArea As Range
Dim searchResult As Range
Dim yourWorkbook As String
Dim rowMinimum As Long
Dim minimumValue As Date
yourWorkbook = "Saving.xlsm"
Set yourWorksheet = Workbooks(yourWorkbook).Worksheets("Sheet1")
personname = Range("D2").Value
finalrow = Range("A100").End(xlUp).Row
For i = 2 To finalrow
If Cells(i, 1) = personname Then
Set searchArea = yourWorksheet.Range(yourWorksheet.Cells(i, 3), _
yourWorksheet.Cells(yourWorksheet.Cells(i, 3).End(xlDown).Row, _
3))
minimumValue = Format(Application.Min(searchArea), "dd/mm/yyyy hh:mm:ss")
Range(Cells(i, 1), Cells(i, 3)).Copy
Range("D100").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
End If
Next i
MsgBox personname & " " & minimumValue
End Sub
My data layout is as follows
<colgroup><col><col><col></colgroup><tbody>
</tbody>
Sub Saving()
Dim personname As String
Dim finalrow As Integer
Dim i As Integer
Dim newSheet As Worksheet
Dim yourWorksheet As Worksheet
Dim searchArea As Range
Dim searchResult As Range
Dim yourWorkbook As String
Dim rowMinimum As Long
Dim minimumValue As Date
yourWorkbook = "Saving.xlsm"
Set yourWorksheet = Workbooks(yourWorkbook).Worksheets("Sheet1")
personname = Range("D2").Value
finalrow = Range("A100").End(xlUp).Row
For i = 2 To finalrow
If Cells(i, 1) = personname Then
Set searchArea = yourWorksheet.Range(yourWorksheet.Cells(i, 3), _
yourWorksheet.Cells(yourWorksheet.Cells(i, 3).End(xlDown).Row, _
3))
minimumValue = Format(Application.Min(searchArea), "dd/mm/yyyy hh:mm:ss")
Range(Cells(i, 1), Cells(i, 3)).Copy
Range("D100").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
End If
Next i
MsgBox personname & " " & minimumValue
End Sub
My data layout is as follows
Teller | Branch | Time |
Peter | Branch Name | 12/5/2017 14:03 |
Peter | Branch Name | 12/5/2017 18:01 |
Peter | Branch Name | 12/5/2017 17:59 |
John | Branch Name2 | 12/5/2017 12:03 |
John | Branch Name2 | 12/5/2017 22:01 |
John | Branch Name2 | 12/5/2017 17:59 |
<colgroup><col><col><col></colgroup><tbody>
</tbody>