CODEWITHSUNDEEP
excelvba
Rhowe
Rhowe

Reputation: 1

Search a string in multiple worksheets

I've been making a calendar system in excel to make phone booking for students (never say you know excel in work is the lesson I've learnt this week), it's all complete but an important part isn't done. A check to make sure that the Student Number has been entered into a calendar.

The string I'm need to search is Student ID which comes from C5 in main sheet. (6 digit number)

I then have 3 different work sheets for July, Aug, and Sept. Each has it's own calendar ranging from A1:Af100.

If it finds the student ID in one of the calendars then it needs to do nothing, but if it doesn't find them a warning message will pop up and end the function

MsgBox "Student ID not entered, please enter student ID into calendar" End Function

I'm stuck which what function to use and getting one to work. Things have gotten stuck in infinity loops of nothing or just crash and goes to debug.

Upvotes: 0

Views: 61

Answers (2)

Rhowe
Rhowe

Reputation: 1

I got something working

    Dim StudID As String
       Dim Rng As Range
       FindString = Sheets("Main Booking").Range("C5").Value
          With Sheets("July").Range("A1:AH100")
             Set Rng = .Find(What:=FindString, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
             If Not Rng Is Nothing Then
                Application.Goto Rng, True
                Call bookingsub
              Else
              Call Augsearch

            End If
         End With

There is one issues with it, If it does find the student ID in the calendar it go to that cell and doesn't stay on the main page, is there any way to stop excel from going to the cell it found the data in?

Upvotes: 0

Carl Colijn
Carl Colijn

Reputation: 1617

I don't know what code you have already, but in broad lines I'd create a function which finds a given String in a given Range, returning if it found it, and call that 3 times for each of the 3 sheets.

In the function you can do a For Each loop using a Range variable, thus iterating over all cells in the given Range. Then, in that loop, test each cell's .Value to see if it contains the value you're interested in (using e.g. InStr). If so, you can exit from the function on the spot (no need to search further). Note that a cell can also contain an error value like #REF!; you can use IsError to check for that first before the InStr call blows up. Or surround the InStr in an On Error Resume + On Error Goto 0 block to skip any failing InStr calls for error cells automatically (if there's an error in a cell it will definitely not contain a student nr.).

Note that for searching with InStr to work, all student numbers should always contain the same number of characters. If not, then you have the risk of false positives where e.g. 001234 can be found in 123456, because leading zeroes might not actually be stored in the cell (only the cell formatting causes them to show up) and won't get added along the way.

The above also assumes that you do a text-based search. If the student nr. is always on it's own in a cell and these cells are also numeric, then you could upgrade that checking code from InStr to a plain = comparison of numbers. This should also work in student numbers have leading zeroes.

Upvotes: 1

Related Questions