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

Question

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.

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.

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.

No, if both are open. and this worked on some but not others? — Scott Craner
– Scott Craner, Commented Aug 25, 2017 at 13:14
@ScottCraner Yeah it works if the column size in workbook 1 is roughly the same size as the row and column sizes in workbook 2. But if the column size of workbook 1 is much smaller than the row and column sizes of workbook 2, this error seems to appear. — F. Lee
– F. Lee, Commented Aug 25, 2017 at 13:16
your data is slightly different than the last question,so I have two question. Is C always numbers? and, here you have the first three lines are the same A and B would you sum the numbers in c and return 9? — Scott Craner
– Scott Craner, Commented Aug 25, 2017 at 13:23

Scott Craner · Accepted Answer · 2017-08-25 13:31:31Z

1

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

answered Aug 25, 2017 at 13:31

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

F. Lee Over a year ago

for future reference can I ask what caused the problem?

Scott Craner Over a year ago

The first formula was written to cover all bases, it will return the first match regardless if numbers or text. With C always being numbers SUMIF is quicker with less overhead. The other formula is an array type formula and uses a lot of overhead. It basically short circuits when referencing large data sets. I should have asked the question about C being number last time. sorry.

F. Lee Over a year ago

One last thing is it possible to set a range for the area that the code looks at. Rather than scanning everything below as it seems to overwrite values if any text from column A in workbook 1 is seen again in workbook 2 that is below the scanned area?

F. Lee Over a year ago

So that the amount of rows that it looks through is fixed.

Scott Craner Over a year ago

you can change the Lastrow to a fixed row: LastRow = 500 and the for loops to end at a specific row and column replaceing everything after the To in both lines to their respective row and column number.

|

Collectives™ on Stack Overflow

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

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related