How to set up an Array formula using Index/match in VBA to get first match cell

Question

I'm trying to set up array formula within a for loop to do a partial match of a string to a list of names on another worksheet called Project Name. This should be the end product I got the formula working in the spreadsheet using the method from exceljet but I ran into an error of "object required" when I tried to covert it to VBA. The cells(i,6) is the location of the string that I am trying to do a partial match to the project names. The column doesn't have to be "6", it is where the Please help. Thanks!

Sub Shortname()

Dim SRng As Variant
Dim SName As Integer
Dim SNrow As Integer
Dim PLcol As Integer
Dim PLrow As Integer

     Worksheets(3).Activate

     SNrow = Cells(Rows.Count, 1).End(xlUp).Row

     SRng = Range(Cells(2, 1), Cells(SNrow, 1)).Value

     Worksheets(2).Activate

     PLcol = Cells(1, Columns.Count).End(xlToLeft).Column + 1
     PLrow = Cells(Rows.Count, 1).End(xlUp).Row

     For i = 2 To PLrow

         Cells(i, PLcol).Value = Application.WorksheetFunction.Index(SRng, Application.WorksheetFunction.Match("TRUE", Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search(SRng.Value, Cells(i, 6))), 0), 1)

     Next i

End Sub

Please read https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba as your code is prone to errors the way it is written now, without exolicitly refferencing worksheets/books — Luuklag, Oct 16 '18 at 05:47

VBasic2008 · Answer 1 · 2018-10-16T05:50:28.927

Mysterious Variable (SRng)

I would write the code something like this (not the solution just a more readable version):

Sub Shortname()
    Dim SRng As Variant
    Dim SName As Integer
    Dim SNrow As Integer
    Dim PLcol As Integer
    Dim PLrow As Integer
    'Missing Declaration
    Dim i As Long
    'To avoid jumping around worksheets do NOT use Activate or Select
    With Worksheets(3)
        SNrow = .Cells(Rows.Count, 1).End(xlUp).Row
        SRng = .Range(Cells(2, 1), Cells(SNrow, 1)).Value
    End With
    With Worksheets(2)
        PLcol = .Cells(1, Columns.Count).End(xlToLeft).Column + 1
        PLrow = .Cells(Rows.Count, 1).End(xlUp).Row
    End With
    With Application.WorksheetFunction
        For i = 2 To PLrow
            'Run-time error 424: Object required
            Worksheets(2).Cells(i, PLcol).Value = .Index(SRng, .Match("TRUE", _
                .IsNumber(.Search(SRng.Value, Cells(i, 6))), 0), 1)
        Next i
    End With
End Sub

The error (mystery) lies in the SRng variable. Why is it declared as variant? If it is a String declare it as String and change the line SRng = .Range(Cells(2, 1), Cells(SNrow, 1)).Value to

SRng = .Range(Cells(2, 1), Cells(SNrow, 1)).Address

If it is a Range object then declare it as Range and remove .Value, but then you still get the error in the For Next loop since you use SRng.Value in the Search part of the statement, but a range has no value (maybe you wanted to use something like this SRng.Cells(i, 6).Value).

If this doesn't help you provide some more info like a sample of the worksheet(s) to see what's in the cells, ranges ... and explain what it is you're searching for in the For Next loop.

First of all, thank you for the cleanup. The way I had it before was very confusing indeed. What I want is this, I have a list of multiple variant names and I need to classify them by a more general project name by doing a partial match. For example, if I have variants named "ash14", "ash15", and "ash16", I want to be able to classify them all as project "ash". So in the worksheet, I used the array formula {=INDEX('Project Name'!A2:A72,MATCH(TRUE,ISNUMBER(SEARCH('Project Name'!A2:A72,'Variant'!B2)),0))} But I want it to do it in VBA and use a loop to do it for all the lines. — user3912244, Oct 16 '18 at 18:43
I don't think the array formula will work in VBA, so it has to be 'translated' to VBA. I've been working on the solution but have come to the conclusion that it is too complicated and I cannot finish it without you providing a sample of the data in columns A on sheet 'Project Name' and B on sheet 'Variant' and an explanation of Cells(i, 6). You should paste it at the end of your initial Question. — VBasic2008, Oct 17 '18 at 06:33
I updated the original question. Hopefully, it clarifies my issue for you. Thank you for your help. — user3912244, Oct 17 '18 at 17:08

How to set up an Array formula using Index/match in VBA to get first match cell

1 Answers1

Mysterious Variable (SRng)