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



## The_Steward (Jan 4, 2023)

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 (Jan 4, 2023)

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 (Jan 4, 2023)

Herakles said:


> 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
> ...


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 (Jan 5, 2023)

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 (Jan 7, 2023)

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 (Jan 7, 2023)

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 (Sunday at 3:52 PM)

Herakles said:


> 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.
> ...


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 (Monday at 1:49 PM)

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 (Monday at 4:11 PM)

Herakles said:


> 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 (Tuesday at 4:18 AM)

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
```


----------



## The_Steward (Jan 4, 2023)

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 (Thursday at 3:15 AM)

Herakles said:


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


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 (Thursday at 7:02 AM)

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 (Thursday at 11:58 PM)

Herakles said:


> What isn't working?
> 
> Use this code with some messages in it to track what is happening.
> 
> ...


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
```


----------

