in VBA index value in array for .Evaluate

[quitethenovice]

Hello all

I'm using the VBA evaluate function to count matching rows. My question is about how to reference a value in an array. Is it a case of changing the way I declare the array or is there a way to index like I've tried?

Thanks!

PS. Below is an extract of the relevant parts of my module.

Dim TechArray As Variant, AssetRng As String, Asset1 As String, Asset2 As String, Asset3 As String, LR1 As String

LR1 = ThisWorkbook.Sheets("Main").Range("A" & Rows.Count).End(xlUp).Row

TechArray = Array("John", "Bob", "Sandra", "Jason")
TechRng = "Q2:Q" & LR1
AssetRng = "E2:E" & LR1
Asset1 = """*sv*"""
Asset2 = """*s/v*"""
Asset3 = """*sluice*"""

Activeworkbook.Sheets("Fig").Range("G2").Value = ThisWorkbook.Sheets("Main").Evaluate("SUMPRODUCT(((ISNUMBER(SEARCH(" & Asset1 & "," & AssetRng & ")))" & _
                                         "+(ISNUMBER(SEARCH(" & Asset2 & "," & AssetRng & ")))+(ISNUMBER(SEARCH(" & Asset3 & "," & AssetRng & "))))" & _
                                         "*((ISNUMBER(SEARCH(" & TechArray(1) & "," & TechRng & ")))+(ISNUMBER(SEARCH(" & TechArray(4) & "," & TechRng & "))))"

 

[Fluff]

You are missing some brackets in the formula

ActiveWorkbook.Sheets("Fig").Range("G2").Value = ThisWorkbook.Sheets("Main").Evaluate("SUMPRODUCT(((ISNUMBER(SEARCH(" & Asset1 & "," & AssetRng & ")))" & _
                                         "+(ISNUMBER(SEARCH(" & Asset2 & "," & AssetRng & ")))+(ISNUMBER(SEARCH(" & Asset3 & "," & AssetRng & "))))" & _
                                         "*((ISNUMBER(SEARCH(" & TechArray(1) & "," & TechRng & ")))+(ISNUMBER(SEARCH(" & TechArray(4) & "," & TechRng & ")))))")

 

[jasonb75]

TechArray(4) might be a problem as well with only 4 elements in the array?

 

[quitethenovice]

@Fluff, thank you.

@jasonb75, am I correct that rather than 1-4 I should be using 0-3 as index reference?

 

[Fluff]

Depends on if your running Option Base 1 or not.
If not then your array will be 0 to 3

 

[quitethenovice]

Thank you both, that makes sense to me now.

 

[Fluff]

Glad we could help & thanks for the feedback.