CODEWITHSUNDEEP
excelvba
User1310
User1310

Reputation: 3

Dim variable error, type mismatch?

I got this code from someone who helped me convert this excel formula to vba. The Excel formula is: =INDEX('C:\Users\Desktop\[Backlog.xlsx]backlog1'!$J:$J,MATCH(A3,'C:\Users\Desktop\[Backlog.xlsx]backlog1'!$W:$W,0))

The code is: 

SetAttr "C:\Users\Desktop\Backlog.xlsx", vbNormal
Dim Backlog As Workbook
Dim bcklog1 As Worksheet
Set Backlog = Workbooks.Open(Filename:="C:\Users\Desktop\Backlog.xlsx", UpdateLinks:=0)
Set bcklog1 = Backlog.Worksheets("backlog1")

Dim result As Variant, test As Variant
Dim frml As Variant, match_row As Variant

frml = "match(A2, " & bcklog1.Range("W:W").Address(external:=True) & ", 0)"
Debug.Print frml
match_row = Evaluate(frml)
Debug.Print match_row

frml = "index(" & bcklog1.Range("J:J").Address(external:=True) & ", " & frml & ")"
Debug.Print frml
result = Evaluate(frml)

test = Application.WorksheetFunction.Index(bcklog1.Range("J:J"), match_row, 1)
Debug.Print test`

I keep getting an error mismatch, I have changed the variables all to Variant and still no success. The sub should use index/match to find values between two different workbooks. Some values will not be found resulting in an "error", which is what I also want to find, the error will represent things I need to focus on. The results should appear in column F:F. I have been stuck on this for a while now, any help is appreciated.

Upvotes: 0

Views: 386

Answers (3)

Excel Hero
Excel Hero

Reputation: 14764

Here is a way that does all of the comparisons an puts them in column F.

This method is interesting from a technical perspective because it uses no VBA loops at all:

Public Sub excelhero()

    Const BACKLOG_WB = "C:\Users\Desktop\Backlog.xlsx"
    Const BACKLOG_WS = "backlog1"

    Dim n&, ws As Worksheet
    Set ws = Workbooks.Open(BACKLOG_WB, 0).Worksheets(BACKLOG_WS)
    With ThisWorkbook.ActiveSheet
        n = .Cells(.Rows.Count, "a").End(xlUp).Row
        .Range("f2:f" & n) = ws.Evaluate("transpose(transpose(index(j:j,n(if(1,match([" & .Parent.Name & "]" & .Name & "!a2:a" & n & ",w:w,))))))")
    End With
    ws.Parent.Close

End Sub

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166835

EDIT - updated to use a loop

I'd do it this way...

Sub test()

    Dim Backlog As Workbook
    Dim bcklog1 As Worksheet
    Set Backlog = Workbooks.Open(Filename:="C:\Users\Desktop\Backlog.xlsx", UpdateLinks:=0)
    Set bcklog1 = Backlog.Worksheets("backlog1")

    Dim  m, test, c
    ' adjust following range as needed
    For each c in ActiveSheet.Range("A2:A200").Cells
        v = c.Value
        If Len(v) > 0 then
            'note no "worksheetfunction" or "no match" will raise an error
            m = Application.Match(v, bcklog1.Range("W:W"), 0)

            'instead we test for no match here...
            If Not IsError(m) Then
                test = bcklog1.Range("J:J").Cells(m).Value
                'Debug.Print test
                c.offset(0, 5).Value = test 'populate colF
            End If
        End If 'cell has a value

    Next c

End Sub

Upvotes: 1

David Zemens
David Zemens

Reputation: 53663

If match_row evaluates to an error type (Error 2042 if the match is not found), the assignment to test = Application.WorksheetFunction.Index(... will fail, because the right-side of the assignment statement cannot evaluate, because you're passing the Error 2042 to the Index function.

If Not IsError(match_row) Then
    test = Application.WorksheetFunction.Index(bcklog1.Range("J:J"), match_row, 1)
Else
    MsgBox "something"
End If

Upvotes: 1

Related Questions