CODEWITHSUNDEEP
vbaexcel
yomomo
yomomo

Reputation: 5

Show information from a certain row

So i'm trying to find the row number of the value "period" and i don't know how to get the correct row number in which the value of "period" is in. Right now the value of "period" is taken as the row number, so when the msgbox pops up it shows the wrong information. This is what my sub looks like:

Sub Oldest()    
    Dim ENROLL_PERIOD As Range
    Dim period As Integer

    Set ENROLL_PERIOD = Range("E2:E" & Rows.Count)
    period = Application.WorksheetFunction.Min(ENROLL_PERIOD)
    If Val(period) And 1 Then
    MsgBox "Enrolled: Spring Semester " & (period / 2 + 1949) & vbNewLine & "Student Id: " & (Cells(period, 12)) & vbNewLine & "Enroll Date: " & (Cells(period, 4)) & vbNewLine & "Program Type: " & (Cells(period, 11))
    Else
    MsgBox "Enrolled: Fall Semester " & (period / 2 + 1949) & vbNewLine & "Student Id: " & (Cells(period, 12)) & vbNewLine & "Enroll Date: " & (Cells(period, 4)) & vbNewLine & "Program Type: " & (Cells(period, 11))
    End If



End Sub

Upvotes: 0

Views: 26

Answers (1)

DisplayName
DisplayName

Reputation: 13386

try this

Dim ENROLL_PERIOD As Range
Dim period As Long
Dim minPeriodRow As Long

Set ENROLL_PERIOD = Range("E2:E" & Rows.Count)
period = Application.WorksheetFunction.Min(ENROLL_PERIOD)
minPeriodRow = ENROLL_PERIOD.Find(what:=period, lookat:=xlWhole, LookIn:=xlValues).Row

Upvotes: 1

Related Questions