Lookup - Type mismatch

0
votes

I cannot find the correct type for my lookup function in vba.

My Excel formula is as following and works fine.

=IF(INDIRECT("'Enclosure4-Workflow_Structure'!C"&MATCH('Enclosure2-Accesses'!A8; 
    'Enclosure4-Workflow_Structure'!A:A; 0))="Create"; 
    IF(LOOKUP(2; 1/('Enclosure5-Workflow_Steps'!A:A=INDIRECT("'Enclosure4-Workflow_Structure'!D"
    &MATCH('Enclosure2-Accesses'!A8; 'Enclosure4-Workflow_Structure'!A:A; 0))); 
    'Enclosure5-Workflow_Steps'!D:D) = "Task"; 'Enclosure2-Accesses'!B8; FALSE); FALSE)

The first if-clause works fine for me but the second if-clause contains the lookup function. This lookup function should come up with "Task", therefore I thought I should set the DIM as String but I constantly receive the error message: "Type mismatch".

The line which throws the error should get the last occurence of a value. This value should correspond to "Task" in the D column. 

lOccurence = WorksheetFunction.Lookup(2, 1 / (Enc5.Range("A:A") = Enc4.Cells(MatchCrt, "D").Value), Enc5.Range("D:D"))

I am curious why the above line causes the error. In Excel the line works without problem. Is the line incorrect or is the DIM type (String) incorrect?

My VBA code is:

Public Sub CopyUserAR2Data()

Dim Enc2 As Worksheet
Dim Enc4 As Worksheet
Dim Enc5 As Worksheet
Dim Enc9 As Worksheet
Dim MatchCrt As Double
Dim lOccurence As String


Set Enc2 = Sheets("Enclosure2-Accesses")
Set Enc4 = Sheets("Enclosure4-Workflow_Structure")
Set Enc5 = Sheets("Enclosure5-Workflow_Steps")
Set Enc9 = Sheets("Enclosure9-Dependency")

MatchCrt = WorksheetFunction.Match(Enc2.Cells(9, "A"), Enc4.Range("A:A"), 0)

lOccurence = WorksheetFunction.Lookup(2, 1 / (Enc5.Range("A:A") = Enc4.Cells(MatchCrt, "D").Value), Enc5.Range("D:D"))


If Enc4.Cells(MatchCrt, "C") = "Create" Then

    Enc9.Cells(2, 1).Value = lOccurence

End If

End Sub
2
excelvbalookup
When in doubt use "Variant" – Luuklag
MatchCrt & lOccurance, will return a long I believe. Also, it may be because it is an array formula, is the working Excel formula an array formula?. – Nathan_Sav
You simply can't do things like 1 / (Enc5.Range("A:A") in VBA, nor can you compare an array to a value using =. You'd have to pass the formula to Evaluate, or loop through the range. – Rory
@Rory, I try to evaluate the lookup function. Can you tell me why I receive the same error message? ar = Evaluate(" =Lookup(2, 1/('Sheet1'!A:A = 'Sheet2'!D2),'Sheet3'!D:D) ") On MrExcel was practical the same formula which was working for somebody. – Alex_P
Are those actually the correct sheet names? It doesn't appear so from your original post. Use the formula exactly as you would put it into a cell. – Rory

2 Answers

0
votes

Your string will cause an error due to you pulling a long value from the Lookup

Best thing to do is call

Dim lOccurence As Variant

If you still the get error, there's something else going on - may a null return value

0
votes

I went with the suggestion from Rory in the comment section and am looping through the array.

If sheet1.Cells(FirstCondition, "C") = "Create" Then    
For d = 1 To numberOfRows
    If (sheet1.Cells(FirstCondition, "D").Value = sheet2.Cells(d, "A").Value And sheet2.Cells(d, "D").Value = "Task") Then
         {"Cell values are entered"}
    End If
Next d
End If

This works now as intended.

@Rory, thank you very much for your patience and assistance!