VBA evaluate function with Index/Match

[FryGirl]

I initially built a Index/Match function in VBA that looped thru the rows, but would like to speed up the process with the Evaluate function. This is where I've gotten to, but it simply repeats the first value from Sheet2.

I must be missing something simple, but can't figure it out. Also, instead of using the sheet name, is it possible to use the sheet codename?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim LastRow As Long
    Dim myRow   As Long
    If Target.CountLarge > 1 Then Exit Sub
    Cancel = True
        
    LastRow = Me.Range("B" & Rows.Count).End(xlUp).Row

    If Not Intersect(Target, Sheet1.Range("E1")) Is Nothing Then
        If Target.Value = "Yes" Then: Target.Value = "No": Else: Target.Value = "Yes"
        
        If Me.Range("ShortName").Value2 = "No" Then
            Me.Range("A4:A" & LastRow).Value = Evaluate("INDEX('Sheet2'!C:C,MATCH(B4:B" & LastRow & ",'Sheet2'!B:B,0))")
        Else
            Me.Range("A4:A" & LastRow).Value = Evaluate("INDEX('Sheet2'!A:A,MATCH(B4:B" & LastRow & ",'Sheet2'!B:B,0))")
        End If

        Me.Columns(1).AutoFit

    End If

End Sub

 

[DanteAmor]

This line is not necessary because in the double click you only modify one cell.
If Target.CountLarge > 1 Then Exit Sub

The next line does not need the colon ":"
If Target.Value = "Yes" Then: Target.Value = "No": Else: Target.Value = "Yes"

It may be so:
If Target.Value = "Yes" Then Target.Value = "No" Else Target.Value = "Yes"

But it can also be like this:
Target.Value = IIf(Target.Value = "Yes", "No", "Yes")

__________________________________________________________________________________
Test if the following is functional for you, otherwise, we would have to find another approach with arrays to make the macro faster.

instead of using the sheet name, is it possible to use the sheet codename?

I also changed the sheet name to the codename.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Not Intersect(Target, Sheet1.Range("E1")) Is Nothing Then
    Cancel = True
    Target.Value = IIf(Target.Value = "Yes", "No", "Yes")
    With Me.Range("A4:A" & Me.Range("B" & Rows.Count).End(xlUp).Row)
      If Me.Range("ShortName").Value = "No" Then
        .Formula = "=INDEX('" & Sheet2.Name & "'!C:C,MATCH(B4,'" & Sheet2.Name & "'!B:B,0))"
      Else
        .Formula = "=INDEX('" & Sheet2.Name & "'!A:A,MATCH(B4,'" & Sheet2.Name & "'!B:B,0))"
      End If
      .Value = .Value
      Me.Columns(1).AutoFit
    End With
  End If
End Sub

 

[FryGirl]

This is great Dante. Thank you for your time.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.