0

I tried the below code but I only get #WERT! as a result, even though I get the correct result when pasting the (str) in Excel directly.. :(

The below code is there to fill blanks. I have a table with blanks that need to be filled following 4 criteria: For row1 = 1 / col1 = 8

Criteria1 = WS.Cells(row1, 4)
Criteria2 = "All Property"
Criteria3 = WS.Cells(row1, 6)
Criteria4 = WS.Cells(3, col1)

As I am just starting out with VBA, I thought using XLOOKUP could be the simplest way.. The below code just takes into account the first 3 Criteria, as I could not find a dynamic way to include the 4th one..

Sub TEST_123()

Dim WB As Workbook
Dim WS As Worksheet
Dim WS2 As Worksheet
Set WB = ActiveWorkbook
Set WS = WB.Sheets("BASIS")
Set WS2 = WB.Sheets("TEST")

Dim arr As Variant
Dim lastrow As Integer

lastrow = WS.Range("D" & WS.Rows.Count).End(xlUp).Row

Dim arrResults() As Variant
Dim Dimension1 As Long, row1 As Long
       
arr = WS.Range("A1:AC" & lastrow)

Dimension1 = UBound(arr, 1)

ReDim arrResults(1 To Dimension1, 1 To 22)
    
Dim str As String

Application.ScreenUpdating = False

       
    For row1 = 1 To Dimension1
    
        If WS.Cells(row1, 8) = "" Then
        
            str = "=XLOOKUP(" & WS.Cells(row1, 4).Address(, , , 1) & "&" & WS.Cells(3, 5).Address(, , , 1) & "&" & WS.Cells(row1, 6).Address(, , , 1) & ";"
            str = str & WS.Range("D1:D" & lastrow).Address(, , , 1) & "&" & WS.Range("E1:E" & lastrow).Address(, , , 1) & "&" & WS.Range("F1:F" & lastrow).Address(, , , 1) & ";"
            str = str & WS.Range("H1:H" & lastrow).Address(, , , 1) & ")"

            arrResults(row1, 1).Formula = Application.Evaluate(str)
            'Debug.Print str
                    
            Else: WS2.Cells(row1, 1) = WS.Cells(row1, 8)

         End If
    Next row1

WS2.Range("A1:A" & lastrow) = arrResults

End Sub

Anything I am doing wrong?

braX
  • 11,506
  • 5
  • 20
  • 33
Elisa R.
  • 21
  • 2
  • 2
    I cannot follow your complete logic but I assume that you simply need `arrResults(row1, 1).Formula = str`. But be aware that in VBA, the formulas needs to be specified in US format, so you will need to use comma as parameter separator, not semicolon – FunThomas Jul 04 '23 at 09:12
  • 1
    Thank you! It worked in deleting the "Application.Evaluate" :D – Elisa R. Jul 04 '23 at 09:18

0 Answers0