Iterating through an Array

[takhtar]

Hi,

I have the following code:

Function Extract(Products As String) As String
Dim softProdArray() As String
Dim out As String
Dim prod As Variant
softProdArray = Split(Products, Chr(10))
For Each prod In softProdArray
If InStr(prod, "ORACLE") > 0 Or InStr(prod, "SYBASE") > 0 Or InStr(prod, "MS SQL") > 0 _
Or InStr(prod, "SYBASE IQ") > 0 Or InStr(prod, "ISQL") > 0 Or InStr(prod, "VERITAS") > 0 Then
out = out & prod & Chr(10)
End If
Next prod
Extract = out
End Function


I am basically pulling in a cell with items separated by carriage returns. I want to take the cell split it into an array delimited by carriage returns then evaluate each element, then output any findins in another variable, appending until I finish looping through the array. That final output I pass back to another cell when I call this funtion from. In that cell I call the function with the following syntax: Extract(A1), where A1 is my data that I am evaluating. I think my major problem is the for loop. The error i am gettting is "That function is not valid," not very descriptive.

Thanks in advance
Tan

 

[Andrew Poulsom]

Welcome to MrExcel.

Try renaming your function. The name Extract is reserved for the Copy to range in Advanced Filter.

 

[Jeffrey Smith]

EXTRACT is already a Excel function. Rename it

 

[takhtar]

Welcome to MrExcel.

Try renaming your function. The name Extract is reserved for the Copy to range in Advanced Filter.

, awesome, that did the trick! All I needed to do was change the name by one letter.

Thanks once again
Tan