check if combination of values are in sheet

[AnnetteTR]

Hi
I have a workbook, where a userform is used to store data for each room.
In a large building it can be several hundreds of rooms - so I want to be able to search for a particular room.
The roomnumber is unique on each floor - that is the combination floor/ roomnumber is unique.
I have a sub that searches roomnumber and floor but it returns the last row with search floor and last row with search roomnumber and that is not nessesarily the same row.

I use this code:

Private Sub cmdSøgRum_Click()
 
      Dim lIsInRow, lIsInRow2 As Variant ' Long
      Dim rngRumnrListe, rngEtageListe As Range
      Dim sRumEtage As String
      Dim sSøgString As String
      Dim søgArray As Variant
      Dim Rum As String, Etage As String
      Dim i As Long, j As Long
 
 
    'Promt for room to find
     sRumEtage = "Which room and floor to search?" & vbNewLine
     sRumEtage = sRumEtage & "Seperate roomnumber and floor by "";"""
    sSøgString = InputBox(sRumEtage, Title:="RoomSearch")
    søgArray = Split(sSøgString, ";")
    Rum = søgArray(0) ' room
    Etage = søgArray(1) ' floor
    MsgBox ("room and floor is: " & Rum & "  " & "" & Etage)
 
With ActiveSheet
        Set rngRumnrListe = Range(.Range("B2"), .Cells(.Rows.Count, "B").End(xlUp))
        Set rngEtageListe = Range(.Range("A2"), .Cells(.Rows.Count, "A").End(xlUp))
 
        ' check room number
        If Not IsError(Application.Match(Rum, rngRumnrListe, 0)) Then
          ' MsgBox "Rumnummer er brugt", vbInformation, vbNullString  ' Besked om at nummeret er brugt
           lIsInRow = Cells.Find(what:=Rum, after:=[B1], Searchorder:=xlByRows, _
            Searchdirection:=xlPrevious).Row
           MsgBox ("Room is in row number  " & lIsInRow)  
 
          ' check floor
          If Not IsError(Application.Match(Etage, rngEtageListe, 0)) Then
           'MsgBox "Floor is used", vbInformation, vbNullString
            lIsInRow2 = Cells.Find(what:=Etage, after:=[B1], Searchorder:=xlByRows, _
            Searchdirection:=xlPrevious).Row
            MsgBox ("Floor is in rownumber  " & lIsInRow2)
            lCurrentRow = lIsInRow2
            VisAktuelRække ' sub to populate the userform with data for the actual room
      End If
      End If
  End With
 
End Sub

I only want to look for the floor, when the roomnumber match.
Can you help me on that?

Thankyou regards
Annette

 

[Gerald Higgins]

Without looking at your code (personally I'm not a VBA expert), what about setting up another field that combines floor number and room number to give you a unique entry for each room, and then get your code to analyse that ?

 

[MickG]

Does it make any difference if you change the "Strings" to "Values" like this:-
I got a result, but not sure its what you wanted

Private Sub cmdSøgRum_Click()
 
      Dim lIsInRow, lIsInRow2 As Variant ' Long
      Dim rngRumnrListe, rngEtageListe As Range
      Dim sRumEtage As String
      Dim sSøgString As String
      Dim søgArray As Variant
      Dim Rum As String, Etage As String
      Dim i As Long, j As Long
 Dim lCurrentRow
 
    'Promt for room to find
     sRumEtage = "Which room and floor to search?" & vbNewLine
     sRumEtage = sRumEtage & "Seperate roomnumber and floor by "";"""
    sSøgString = InputBox(sRumEtage, Title:="RoomSearch")
    søgArray = Split(sSøgString, ";")
    Rum = søgArray(0) ' room
    Etage = søgArray(1) ' floor
    MsgBox ("room and floor is: " & Rum & "  " & "" & Etage)
 
With ActiveSheet
        Set rngRumnrListe = Range(.Range("B2"), .Cells(.rows.Count, "B").End(xlUp))
        Set rngEtageListe = Range(.Range("A2"), .Cells(.rows.Count, "A").End(xlUp))
 
        ' check room number
        If Not IsError(Application.match(Val(Rum), rngRumnrListe, 0)) Then
          ' MsgBox "Rumnummer er brugt", vbInformation, vbNullString  ' Besked om at nummeret er brugt
           lIsInRow = Cells.Find(what:=Val(Rum), after:=[B1], Searchorder:=xlByRows, _
            Searchdirection:=xlPrevious).row
           MsgBox ("Room is in row number  " & lIsInRow)
 
          ' check floor
          If Not IsError(Application.match(Val(Etage), rngEtageListe, 0)) Then
           'MsgBox "Floor is used", vbInformation, vbNullString
            lIsInRow2 = Cells.Find(what:=Val(Etage), after:=[B1], Searchorder:=xlByRows, _
            Searchdirection:=xlPrevious).row
            MsgBox ("Floor is in rownumber  " & lIsInRow2)
            lCurrentRow = lIsInRow2
            'VisAktuelRække ' sub to populate the userform with data for the actual room
      End If
      End If
  End With
 
End Sub

 

[Sektor]

Sub GetFloor()
    
    'Set reference: Tools -> References -> Microsoft VBScript Regular Expressions 5.5
    
    Dim arr As Variant, userInput As Variant
    Dim sFloor As String, sRoom As String
    Dim i As Variant, re As New RegExp
    
    re.Pattern = "\d+;\d+"
        
    userInput = InputBox("Floor;Room?", "Find Floor")
    If userInput = "False" Or Len(userInput) = 0 Then Exit Sub
    
    If Not re.test(userInput) Then
        MsgBox "Incorrect format", vbCritical, "Error"
    End If
    
    sFloor = Split(userInput, ";")(0)
    sRoom = Split(userInput, ";")(1)
    
    arr = Range("A2").CurrentRegion
    
    For i = 1 To UBound(arr)
        If arr(i, 1) = sFloor And arr(i, 2) = sRoom Then
            MsgBox "Room and floor found!"
            Cells(i + 1, "A").Activate
            Exit For
        End If
    Next

End Sub

 

[AnnetteTR]

Hi
Thanks for your answers - I gave up using the match funktion - in stead I use a "for - next" loop like this:

Private Sub cmdSøgRum_Click()
 
     ' Find rummet
      Dim rngRumnrListe, rngEtageListe As Range
      Dim sRumEtage As String
      Dim sSøgString As String
      Dim søgArray As Variant
      Dim Rum As String, Etage As String
      Dim i As Long
 
     'Promt for room to find
     sRumEtage = "Hvilket rum og etage søges?" & vbNewLine
     sRumEtage = sRumEtage & "Indtast rumnr og etage adskilt af "";"""
     sSøgString = InputBox(sRumEtage, Title:="Find room")
     søgArray = Split(sSøgString, ";")
     Rum = søgArray(0)
     Etage = søgArray(1)
 
With ActiveSheet
        Set rngRumnrListe = Range(.Range("B2"), .Cells(.Rows.Count, "B").End(xlUp))
        Set rngEtageListe = Range(.Range("A2"), .Cells(.Rows.Count, "A").End(xlUp))
 
      For i = 1 To rngRumnrListe.Rows.Count
          If rngRumnrListe.Cells(i).Value = Rum Then
                  MsgBox ("room is in line" & i + 1)
                  If rngEtageListe.Cells(i).Value = Etage Then
                    MsgBox ("floor is also in line" & i + 1)
                    lCurrentRow = i + 1
                    VisAktuelRække  ' this is a funktion to populate a userform
                  Else
                  End If
          Else
          End If
      Next i
 
  End With
 
End Sub

This does the job.

Regards
Annette