Using arrays within a worksheet function in VBA

[al_noggin]

Hello,
I am looking to find a cell within a single column whose 10 left letters equals some text. Specifically in one column, I have a bunch of blank cells except for one cell that begins with the text: "Not Enough". I am looking to find a way to check that column to see if a cell like that exists. In Excel, I simply use the following array formula:
{=MATCH("Not Enough",LEFT('Output'!$D$7:$D$500,10),0)}

That works great, but I do not know how to do it in VBA. Any help would be much appreciated.

 

[Jerry Sullivan]

Hi al_noggin,

You can use the Worksheet.Evaluate Method to do that.

 Dim sAddr As String
 Dim vResult As Variant
 
 With Sheets("Output")
   sAddr = .Range("D7:D500").Address(External:=True)
   vResult = .Evaluate("=MATCH(""Not Enough"",LEFT(" & sAddr & ",10),0)")
   Debug.Print vResult
 End With

 

[al_noggin]

Sorry for the slow response. Thanks so much!