Search code examples
excelworksheet-functionvba

Excel 2010 vba Can I reference the result of a worksheetfunction in another formula?

I am trying to reference the result of an Application.WorksheetFunction in another Application.WorksheetFunction but keep getting Error 2029. I'm not familiar with the proper syntax for doing this and am hoping for some help. My code is as follows:

    Set ANoteColID = Application.WorksheetFunction
    ANoteColID = [VLOOKUP('Prior Year'!C4,CollateralData!G3:CZ500,31,0)]
    Set BNoteLookUp = Application.WorksheetFunction
    BNoteColId = [MATCH(ANoteColID &2,(CollateralData!AK3:AK500)&(CollateralData!CZ3:CZ500),0)]
Solution

  • Can you try this (untested) As you are using evaluate (equivalent to the square brackets) you don't need to use worksheetfunction at all.

    ANoteColID = Evaluate("VLOOKUP('Prior Year'!C4,CollateralData!G3:CZ500,31,0)")
BNoteColId = Evaluate("MATCH(" & ANoteColID & "2,(CollateralData!AK3:AK500)&(CollateralData!CZ3:CZ500),0)")