Finding column letter and Min/Max

[JSA]

My overall goal is to find the earliest and latest date in a specific column, from what i can tell in order to do that i first need to find which column the data is in(which ive done) then convert that column number to the column letter, then use that value in application.max and application.min to return those values.

I'm having a problem with is converting the column number to its corresponding letter then applying that value in application.max

I've tried other examples on here but either they wont work with what I'm trying to accomplish or i am not understanding how to use the code properly

ex. We find header "NOM_DATE" in column 14, which is column N, it then returns a Min value of 4-10-11 and a max value of 4-16-11


Public Sub Work()

Dim AName As String
Dim i As Integer
Dim ADate As String
Dim ATime As String
Dim AId As Variant
Dim CAName As Integer
Dim CADate As Integer
Dim CATime As Integer
Dim CurVal
Dim n As Integer


'find ADate, AName and ATime column #'s and assign numbers to CADate, CAName, CATime

CurVal = Sheet2.Cells(1, 1)

While CurVal <> ""

If UCase(CurVal) = "EMP_SHORT_NAME" Then
CAName = n
ElseIf UCase(CurVal) = "TOT_MI" Then
CATime = n
ElseIf UCase(CurVal) = "NOM_DATE" Then
CADate = n
End If

n = n + 1
CurVal = Sheet2.Cells(1, n)

Wend

'set initial row count
i = 2

'set initial locations for agent name, date and time
AName = Sheet2.Cells(i, CAName)
ADate = Sheet2.Cells(i, CADate)
ATime = Sheet2.Cells(i, CATime)

'set min and max values for date and place them in sheet1 g5/g6




' place headers
Sheet3.Cells(1, 1).Value = "CSR"
Sheet3.Cells(1, 2).Value = "DATE"
Sheet3.Cells(1, 3).Value = "Logged On"
Sheet4.Cells(1, 1).Value = "CSR"
Sheet4.Cells(1, 2).Value = "DATE"
Sheet4.Cells(1, 3).Value = "Logged On"

While AName <> ""
'Find agent ID using AName
AId = WorksheetFunction.VLookup(AName, Worksheets("Agent ID").Range("E:G"), 3, False)

'Copy agent name to Payroll output
Sheet3.Cells(i, 1).Value = AId

'Copy date to Payroll output
Sheet3.Cells(i, 2).Value = ADate

'convert agent time to seconds and copy agent time to Payroll output
Sheet3.Cells(i, 3).Value = ATime * 60


'Copy agent name to TL output
Sheet4.Cells(i, 1).Value = AName
'Copy date to TL output
Sheet4.Cells(i, 2).Value = ADate
'convert agent time to hours and copy agent time to TL output
Sheet4.Cells(i, 3).Value = Format(ATime / 1440, "hh:mm")


' increase i value by 1
i = i + 1
'reset AName, ADate and ATime to reflect new i value
AName = Sheet2.Cells(i, CAName)
ADate = Sheet2.Cells(i, CADate)
ATime = Sheet2.Cells(i, CATime)
Wend


End Sub

 

[Jerry Sullivan]

... i first need to find which column the data is in(which ive done) then convert that column number to the column letter, then use that value in application.max and application.min to return those values.

There is no need to convert the Column number to a Column letter to use the Min and Max functions. Try something like...

    Dim dtMax As Date
    dtMax = Application.Max(Sheets("Sheet1").Columns(14))
    MsgBox dtMax