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, Aug 25 '17 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, Aug 25 '17 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, Aug 25 '17 at 13:23
@ScottCraner C is always numbers and yes sum the first three lines would be ideal — F. Lee, Aug 25 '17 at 13:38

score 1 · Accepted Answer · answered Aug 25 '17 at 13:31

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 '17 at 13:31

Scott Craner

148,073
10
49
81

for future reference can I ask what caused the problem? – F. Lee Aug 25 '17 at 13:46
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. – Scott Craner Aug 25 '17 at 13:51
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 Aug 25 '17 at 13:54
So that the amount of rows that it looks through is fixed. – F. Lee Aug 25 '17 at 13:56
1

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. – Scott Craner Aug 25 '17 at 13:58
'For i = 2 To ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Row' changing this to 'For i = 2 To ws2.Cells(50, 1).End(xlUp).Row' ? The overwriting only occurs in worksheet 2. – F. Lee Aug 25 '17 at 14:02
`For i = 2 to 100` and for j = 2 to 50` where your data on sheet 2 has 100 rows and 50 columns – Scott Craner Aug 25 '17 at 14:03

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

1 Answers1