VBA evaluate function with Index/Match

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,225
Office Version
  1. 2016
Platform
  1. Windows
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?

VBA Code:
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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
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.

VBA Code:
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,494
Messages
5,602,004
Members
414,490
Latest member
Rip181

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top