Search code examples
excelvbaindexingevaluate

Excel VBA - Evaluate and index function returning #VALUE! error

So I have an macro for a file in which there are three columns. The macro detect specific columns and rows in another workbook for my three columned file. See image below for workbook 1.

input

Then it should look at the rows and columns of workbook 2 (see below) and find the position of column A and B that match from workbook 1 and paste in the corresponding value of column C into workbook 2 (the value 1). However I keep getting #VALUE! error and I don't know why its happening.

errors

FYI - the columns continue to 51 but cant fit in the image

Below is the code for this macro:

Sub Location()
    Dim i As Long, k As Long, ws1 As Worksheet, ws2 As Worksheet
    Dim lastrow As Long

    Set ws1 = Workbooks("Book3.xlsm").Worksheets("Sheet1")
    Set ws2 = Workbooks("Book4.xlsm").Worksheets("Sheet1")
    lastrow = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row
    For i = 2 To ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Row
        For k = 2 To ws2.Cells(1, ws2.Columns.Count).End(xlToLeft).Column
            ws2.Cells(i, k).Value = ws2.Evaluate("IFERROR(INDEX(" & ws1.Range("B1:B" & lastrow).Address(0, 0, xlA1, 1) & ",AGGREGATE(15,6,ROW(" & ws1.Range("A1:A" & lastrow).Address(0, 0, xlA1, 1) & ")" & _
            "/((" & ws1.Range("A1:A" & lastrow).Address(0, 0, xlA1, 1) & " = " & ws2.Cells(i, 1).Address(0, 0) & ")*(" & ws1.Range("C1:C" & lastrow).Address(0, 0, xlA1, 1) & "=" & _
            ws2.Cells(1, k).Address(0, 0) & ")),1)),"""")")
        Next k
    Next i

End Sub

Any help? Thanks.

Solution

  • I should have thought of this formula first:

    Sub Location()
    Dim i As Long, k As Long, ws1 As Worksheet, ws2 As Worksheet
    Dim LastRow As Long

    Set ws1 = Workbooks("Book3.xlsm").Worksheets("Sheet1")
    Set ws2 = Workbooks("Book4.xlsm").Worksheets("Sheet1")
    LastRow = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row
    For i = 2 To ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Row
        For k = 2 To ws2.Cells(1, ws2.Columns.Count).End(xlToLeft).Column
            ws2.Cells(i, k).Value = Application.WorksheetFunction.SumIfs(ws1.Range("C1:C" & LastRow), ws1.Range("A1:A" & LastRow), ws2.Cells(i, 1), ws1.Range("B1:B" & LastRow), ws2.Cells(1, k))
            If ws2.Cells(i, k).Value = 0 Then ws2.Cells(i, k).Value = ""
        Next k
    Next i

End Sub