Find matching cells in array with VBA + opening sheet when found

[The_Steward]

I have 2 arrays in 2 columns in the same sheet. The first array displays the sheet name for all clients. The second array displays all client names. When a client's name has not been inputted into their sheet, the second array defaults to match the name of the client sheet.

i.e
Array 1: Client 1, Client 2, Client3

Array 2: John, Michael, Client3 (No name listed for client on sheet so defaults to sheet name)

I want to create a button that will find the next sheet where a client's name has not been added (The two arrays match) and open the sheet.

How might I do this?

 

[The_Steward]

Try this. The first column reference and offset must be correct.

Private Sub cmdClient_Click()
Dim rng As Range
Dim lngLastRow As Long
Dim strSheet As String

On Error GoTo Err_Handler

    lngLastRow = Cells(Rows.Count, "H").End(xlUp).Row

    For Each rng In Range("H2:H" & lngLastRow).Cells

        If rng.Value = rng.Offset(0, 2).Value Then
            rng.Offset(0, 2).Select
            strSheet = rng.Value
            Worksheets(strSheet).Activate
            Exit Sub
        End If
   
    Next rng

Exit_Handler:

    Exit Sub
   
Err_Handler:

    MsgBox "An error has occured. Maybe the sheet does not exist." & vbCrLf & vbCrLf & Err.Number & " : " & Err.Description & ".", vbInformation, "Error"
   
    Resume Exit_Handler

End Sub

Still not working. i'll continue to fiddle around with it and see if I can figure out the issue. I feel like you might be right about the offset values being wrong, but I just can't see how they are atm.

 

[Herakles]

What isn't working?

Use this code with some messages in it to track what is happening.

Use a small data set with matching cells a few rows down.

Is the button actually calling the code?

Are you still in Design Mode?

Private Sub cmdClient_Click()
Dim rng As Range
Dim lngLastRow As Long
Dim strSheet As String

On Error GoTo Err_Handler

    lngLastRow = Cells(Rows.Count, "H").End(xlUp).Row

    For Each rng In Range("H2:H" & lngLastRow).Cells

        MsgBox rng.Address & " " & rng.Value & " - " & rng.Offset(0, 2).Value

        If rng.Value = rng.Offset(0, 2).Value Then
            rng.Offset(0, 2).Select
            strSheet = rng.Value
            Worksheets(strSheet).Activate
            MsgBox "These two match" & vbCrLf & rng.Address & " " & rng.Value & " -" & rng.Offset(0, 2).Value
            Exit Sub
        End If
    
    Next rng

Exit_Handler:

    Exit Sub
    
Err_Handler:

    MsgBox "An error has occured. Maybe the sheet does not exist." & vbCrLf & vbCrLf & Err.Number & " : " & Err.Description & ".", vbInformation, "Error"
    
    Resume Exit_Handler

End Sub

 

[The_Steward]

What isn't working?

Use this code with some messages in it to track what is happening.

Use a small data set with matching cells a few rows down.

Is the button actually calling the code?

Are you still in Design Mode?

Private Sub cmdClient_Click()
Dim rng As Range
Dim lngLastRow As Long
Dim strSheet As String

On Error GoTo Err_Handler

    lngLastRow = Cells(Rows.Count, "H").End(xlUp).Row

    For Each rng In Range("H2:H" & lngLastRow).Cells

        MsgBox rng.Address & " " & rng.Value & " - " & rng.Offset(0, 2).Value

        If rng.Value = rng.Offset(0, 2).Value Then
            rng.Offset(0, 2).Select
            strSheet = rng.Value
            Worksheets(strSheet).Activate
            MsgBox "These two match" & vbCrLf & rng.Address & " " & rng.Value & " -" & rng.Offset(0, 2).Value
            Exit Sub
        End If
   
    Next rng

Exit_Handler:

    Exit Sub
   
Err_Handler:

    MsgBox "An error has occured. Maybe the sheet does not exist." & vbCrLf & vbCrLf & Err.Number & " : " & Err.Description & ".", vbInformation, "Error"
   
    Resume Exit_Handler

End Sub

It seems the offset value is not coming up (please see screenshot).

When I use the + button (Graphic4 - Which I have exchanged for cmdClient in sub) it calls the sub fine and the msgbox comes up.

Nope in ready mode.

The changes I from yours was changing sub name, having row number start at 4 in H column, and ensuring lrow was referencing "Code and Data Centre" Sheet.

This is how code currently looks:

Private Sub Graphic4_Click()
Dim rng As Range
Dim lngLastRow As Long
Dim strSheet As String


On Error GoTo Err_Handler

    lngLastRow = workSheets("Code and Data Centre").Cells(Rows.Count, "H").End(xlUp).Row

     For Each rng In Range("H4:H" & lngLastRow).Cells

        MsgBox rng.Address & " " & rng.Value & " - " & rng.Offset(0, 2).Value

        If rng.Value = rng.Offset(0, 2).Value Then
            rng.Offset(0, 2).Select
            strSheet = rng.Value
            workSheets(strSheet).Activate
            MsgBox "These two match" & vbCrLf & rng.Address & " " & rng.Value & " -" & rng.Offset(0, 2).Value
            Exit Sub
        End If
    
    Next rng

Exit_Handler:

    Exit Sub
    
Err_Handler:

    MsgBox "An error has occured. Maybe the sheet does not exist." & vbCrLf & vbCrLf & Err.Number & " : " & Err.Description & ".", vbInformation, "Error"
    
    Resume Exit_Handler

End Sub

 

[The_Steward]

What isn't working?

Use this code with some messages in it to track what is happening.

Use a small data set with matching cells a few rows down.

Is the button actually calling the code?

Are you still in Design Mode?

Private Sub cmdClient_Click()
Dim rng As Range
Dim lngLastRow As Long
Dim strSheet As String

On Error GoTo Err_Handler

    lngLastRow = Cells(Rows.Count, "H").End(xlUp).Row

    For Each rng In Range("H2:H" & lngLastRow).Cells

        MsgBox rng.Address & " " & rng.Value & " - " & rng.Offset(0, 2).Value

        If rng.Value = rng.Offset(0, 2).Value Then
            rng.Offset(0, 2).Select
            strSheet = rng.Value
            Worksheets(strSheet).Activate
            MsgBox "These two match" & vbCrLf & rng.Address & " " & rng.Value & " -" & rng.Offset(0, 2).Value
            Exit Sub
        End If
   
    Next rng

Exit_Handler:

    Exit Sub
   
Err_Handler:

    MsgBox "An error has occured. Maybe the sheet does not exist." & vbCrLf & vbCrLf & Err.Number & " : " & Err.Description & ".", vbInformation, "Error"
   
    Resume Exit_Handler

End Sub

Thankyou for all your help.

I realised I forgot to specify the sheet where rng.value was located so I added that and removed rng.offset select and worked perfectly.

Here is final code

Private Sub Graphic4_Click()
Dim rng As Range
Dim lngLastRow As Long
Dim strSheet As String


On Error GoTo Err_Handler

    lngLastRow = workSheets("Code and Data Centre").Cells(Rows.Count, "H").End(xlUp).Row

     For Each rng In workSheets("Code and Data Centre").Range("H4:H" & lngLastRow).Cells

        If rng.Value = rng.Offset(0, 2).Value Then

            strSheet = rng.Value
            workSheets(strSheet).Activate
            Range("A1").Select
            

            
            Exit Sub
        End If
    
    
    If Not rng.Value = rng.Offset(0, 2).Value Then
            
            MsgBox "All SIL Houses spots are full. Delete a SIL House or create a newish template"
        End If
        
    Next rng
Exit_Handler:

    Exit Sub
    
Err_Handler:

    MsgBox "An Error has occurred. Please check there are available slots."
    
    Resume Exit_Handler

End Sub