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?

 

[Herakles]

So when it finds the first row without a client name will you be entering the client name so that the button code
will not find it again?

If not then do you want it to go to the next row where the client name has not been entered when the
button is pressed again.

Are you able to create the button itself?

 

[The_Steward]

So when it finds the first row without a client name will you be entering the client name so that the button code
will not find it again?

If not then do you want it to go to the next row where the client name has not been entered when the
button is pressed again.

Are you able to create the button itself?

Yes, the idea is that when a user clicks the button, it will take them to the next empty sheet, and then they will enter the client name in that sheet. So next time they click the button it will open the next empty sheet.

All the sheets will have been created prior, so clicking the button will just take them to an already created empty sheet.

No, if the user does not enter a client name in the sheet they opened, then next time they click the button they should be directed back to that same sheet. Otherwise there may be empty sheets being skipped over.

I can create the button, the issue is creating the macro that I will attach to the button.

 

[Herakles]

Does this do the job?

You will need to change the sub name 'cmdClient' to match the name of the button.

You will also need to change the column letter from 'A' as appropriate in this line
For Each rng In Range("A2:A" & lngLastRow).Cells

The code could be written to create the worksheet if it does not exist and to update the sheets
with the client name.

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

On Error GoTo Err_Handler

    lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For Each rng In Range("A2:A" & lngLastRow).Cells
        
        If rng.Value = rng.Offset(0, 1).Value Then
            rng.Offset(0, 1).Select
            Worksheets(rng.Value).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

 

[The_Steward]

Thanks! I edited it to suit but last line of this part not working:

 lngLastRow = workSheets("Code and Data Centre").Cells(Rows.Count, 8).End(xlUp).Row
    
    For Each rng In Range("H4:H" & lngLastRow).Cells
        
        If rng.Value = rng.Offset(0, 2).Value Then
            rng.Offset(0, 2).Select
            workSheets(rng.Value).Activate

Any ideas?

Should be activating the sheet name which is matched in H5 and K7 below

 

[Herakles]

Try this.

A technique that I had to use on another project but I'm not sure why as it worked on my
machine without it.

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, "A").End(xlUp).Row
    
    For Each rng In Range("A2:A" & lngLastRow).Cells
        
        If rng.Value = rng.Offset(0, 1).Value Then
            rng.Offset(0, 1).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

 

[The_Steward]

Try this.

A technique that I had to use on another project but I'm not sure why as it worked on my
machine without it.

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, "A").End(xlUp).Row
   
    For Each rng In Range("A2:A" & lngLastRow).Cells
       
        If rng.Value = rng.Offset(0, 1).Value Then
            rng.Offset(0, 1).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. strSheet value is coming up as '''' so it appears that its just not able to find the matching values for some reason. Doesn't make any sense to me.

 

[Herakles]

Are you trying to compare data in columns H and K on different rows?

There is no data in cell K7 in the sample attachment you posted earlier.

 

[The_Steward]

Are you trying to compare data in columns H and K on different rows?

There is no data in cell K7 in the sample attachment you posted earlier.

Whoops I meant H5 and J5, the rng value should match offset value here. I put the offset to (0,2) to accommodate this.

 

[Herakles]

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