FINDSERIES (Excel 03)

Topic

New Reply

I’m trying to use a formula (FINDSERIES) and I’m getting a #NAME? error. I have the following setup in VB. I’ve used the same function successfully in other excel files, but I’m failing here. What am I doing wrong this time?

Public Function FindSeries(TRange As Range, MatchWith As String)

For Each cell In TRange
If cell.Value = MatchWith Then
x = x & cell.Offset(0, 1).Value & “, ”
End If
Next cell

FindSeries = Left(x, (Len(x) – 2))

End Function

Reply | Quote

Replies

Is the module containing the function in the same book as where you are trying to use the function?
If you are trying to use it in a workbook other than the one in which it is located, you must refer to the location book:

Code:

=Book1!FINDSERIES(a1:a10,"ASDF")

I would also suggest using Application.Volatile in your function so that it updates when a cell is changed

Code:

Public Function FindSeries(TRange As Range, MatchWith As String)
Application.Volatile
For Each cell In TRange
If cell.Value = MatchWith Then
x = x & cell.Offset(0, 1).Value & ", "
End If
Next cell

FindSeries = Left(x, (Len(x) - 2))

End Function

Reply | Quote

Did you intend to post this in Excel forum, or are you trying to use Automation of Excel in an Access VBA module? If it’s the latter the automation bit would need to be defined first.

Reply | Quote

Yes. My apologies as this was meant to be posted in spreadsheets instead of databases. I tried the adjustments and I’m still receiving the same error. Strange!

Reply | Quote

Can you post a sanitized book where you are receiving the error? You function works fine when I tried to run it.

Reply | Quote

I’ll move the thread to Spreadsheets then. Thanks for the reply.

Reply | Quote

The function must be in a normal module (not worksheet, workbook or class module) and you have to have macros enabled.

Reply | Quote

That was it! I had it in the workbook, not in a normal module. I put it in the wrong place. Thanks everyone!

Reply | Quote