Multiple Find commands to create multiple variables

[musoguy]

I am having huge problems with the code I wrote, i.e. it doesn't work most of the time! I have two worksheets set up like this example:

Excel Workbook
	A	B	C	D	E
3	PERIOD	INFO	SPACE 1	SPACE 2	ETC
4
5	1	TYPE	Music	Acting
6		CLASS	Singing Tech	Shakespeare
7		LEVEL	2 / 3	1/2
8		TEACHER 1	Teacher's Name Appears Here	Teacher's Name Appears Here
9		TEACHER 2	Or here (never both)	Or here (never both)
MonWedFri

Excel 2010



This example shows Period 1. Rows 8 and 9 contain a staff person's name. There is a different location in each column from C to AH. Each staff member will only ever appear in rows 8 and 9 once in total, but they may not appear at all.

There is also a Period 2 and 3 on this sheet with the staff names appearing in rows 13-14 and 18-19 (the Columns remain the same)

Then finally there is a second sheet called "TueThuSat" which is a replica of this sheet, but for periods 4, 5 and 6.

What I am trying to do is create a code that will allow the user to choose a staff name and a msgbox will appear with what they are teaching each period. If their name does not appear, I'd like it to say "Free".

The code I have that is not working very well is:

Sub ShowClasses()


Dim c, FirstAddress, SecondAddress, ThirdAddress, FourthAddress, FifthAddress, SixthAddress As Range
Dim StaffMember, FirstPeriod, SecondPeriod, ThirdPeriod, FourthPeriod, FifthPeriod, SixthPeriod As String


StaffMember = InputBox(Prompt:="Choose a Staff member.", _
       Title:="STAFF MEMBER")


Sheets("MonWedFri").Select
With ActiveSheet

        Set c = Range("C8:AH8").Find(StaffMember, LookIn:=xlValues)
        If Not c Is Nothing Then
        FirstAddress = c.Address
        FirstPeriod = Range(FirstAddress).Offset(-2, 0)
        Else: If c Is Nothing Then _
        Set c = Range("C9:AH9").Find(StaffMember, LookIn:=xlValues)
        If Not c Is Nothing Then
        FirstAddress = c.Address
        FirstPeriod = Range(FirstAddress).Offset(-3, 0)
        Else: If c Is Nothing Then FirstPeriod = "FREE"
        End If
        End If

        Set c = Range("C13:AH13").Find(StaffMember, LookIn:=xlValues)
        If Not c Is Nothing Then
        SecondAddress = c.Address
        SecondPeriod = Range(SecondAddress).Offset(-2, 0)
        Else: If c Is Nothing Then _
        Set c = Range("C14:AH14").Find(StaffMember, LookIn:=xlValues)
        If Not c Is Nothing Then
        SecondAddress = c.Address
        SecondPeriod = Range(SecondAddress).Offset(-3, 0)
        Else: If c Is Nothing Then SecondPeriod = "FREE"
        End If
        End If

        Set c = Range("C18:AH18").Find(StaffMember, LookIn:=xlValues)
        If Not c Is Nothing Then
        ThirdAddress = c.Address
        ThirdPeriod = Range(ThirdAddress).Offset(-2, 0)
        Else: If c Is Nothing Then _
        Set c = Range("C19:AH19").Find(StaffMember, LookIn:=xlValues)
        If Not c Is Nothing Then
        ThirdPeriod = Range(ThirdAddress).Offset(-3, 0)
        Else: If c Is Nothing Then ThirdPeriod = "FREE"
        End If
        End If
        
End With

Sheets("TueThuSat").Select
With ActiveSheet

        Set c = Range("C8:AH8").Find(StaffMember, LookIn:=xlValues)
        If Not c Is Nothing Then
        FourthAddress = c.Address
        FourthPeriod = Range(FourthAddress).Offset(-2, 0)
        Else: If c Is Nothing Then _
        Set c = Range("C9:AH9").Find(StaffMember, LookIn:=xlValues)
        If Not c Is Nothing Then
        FourthAddress = c.Address
        FourthPeriod = Range(FourthAddress).Offset(-3, 0)
        Else: If c Is Nothing Then FourthPeriod = "FREE"
        End If
        End If

        Set c = Range("C13:AH13").Find(StaffMember, LookIn:=xlValues)
        If Not c Is Nothing Then
        FifthAddress = c.Address
        FifthPeriod = Range(FifthAddress).Offset(-2, 0)
        Else: If c Is Nothing Then _
        Set c = Range("C14:AH14").Find(StaffMember, LookIn:=xlValues)
        If Not c Is Nothing Then
        FifthAddress = c.Address
        FifthPeriod = Range(FifthAddress).Offset(-3, 0)
        Else: If c Is Nothing Then FifthPeriod = "FREE"
        End If
        End If

        Set c = Range("C18:AH19").Find(StaffMember, LookIn:=xlValues)
        If Not c Is Nothing Then
        SixthAddress = c.Address
        SixthPeriod = Range(SixthAddress).Offset(-2, 0)
        Else: If c Is Nothing Then _
        Set c = Range("C14:AH14").Find(StaffMember, LookIn:=xlValues)
        If Not c Is Nothing Then
        SixthAddress = c.Address
        SixthPeriod = Range(SixthAddress).Offset(-3, 0)
        Else: If c Is Nothing Then SixthPeriod = "FREE"
        End If
        End If
        
End With

Sheets("Info").Select

MsgBox "Period 1: " & FirstPeriod & " Period 2: " & SecondPeriod & " Period 3: " & ThirdPeriod & " Period 4: " & FourthPeriod & " Period 5: " & FifthPeriod & " Period 6: " & SixthPeriod



End Sub

The inputbox will ultimately be replaced by a userform combobox with a list of staff members, I just wanted to get the coding right first.
The same with the formatting of the text in the msgbox at the end.

Really hope someone can help. I rarely have a need to use VBA, and so I'm sure my coding is terrible! Sorry in advance!

James

 

[Jerry Sullivan]

Hi James,

Since the steps of finding a Teacher's name and returning the class is repeated often,
you might consider breaking that out as a separate function.

Then you can get the class names by calling the function and using Days
of Week, Period and Teacher as parameters.

Private Function GetClass(strSheetName As String, _
    lngPeriod As Long, strStaffName As String) As String
 
    Dim cIdx As Range, cMatch As Range
 
    With Sheets(strSheetName)
    '---find row with period number
        Set cIdx = .Columns("A").Find(What:=lngPeriod, _
                After:=.Range("A1"), LookIn:=xlValues, _
                LookAt:=xlWhole, SearchDirection:=xlNext)
        If cIdx Is Nothing Then
            GetClass = "Error: Period " & lngPeriod & " not found."
            Exit Function
        Else
            '---find Staff Name for this period
            Set cMatch = cIdx.Offset(3, 0).Resize(2, 36).Find _
                (What:=strStaffName, LookIn:=xlValues, LookAt:=xlPart)
            If cMatch Is Nothing Then
                GetClass = "FREE"
                Exit Function
            Else '---find Class name
                GetClass = cMatch.Offset(cIdx.Row - cMatch.Row, 0)
            End If
        End If
    End With
End Function

This greatly simplifies the Sub that builds your display message...

Sub ShowClasses()
    Dim StaffMember As String, strMessage As String
    Dim n As Long
 
    StaffMember = InputBox(Prompt:="Choose a Staff member.", _
         Title:="STAFF MEMBER")
 
    For n = 1 To 9
        strMessage = strMessage & _
            "Period " & n & ": " & _
            GetClass("MonWedFri", n, StaffMember) & vbCr
    Next n
    MsgBox strMessage
End Sub

Just let me know if you have any problems getting this to work.

 

[musoguy]

Jerry, thanks for the reply. I had actually worked out a workaround that seems to more or less be along the same lines of thinking. I created another sheet, which pulled in all the information I needed in consecutive columns by teacher, and then was able to easily use a msgbox using that information. However, your reply really helps, as it has shown me a better way to achieve the result in the future, and is definitely a technique I will use. Thanks again.

James