Cell function to Makro

[Lallo]

I'm trying to build a function to check if an entered client-ID already exists in a list.

I have a macro that uses the value of a cell

The cell contains the function:

{=SUM(--ISNUMBER(FIND(I5,I7:I5000)))}

And checks whether it is 0 (zero) or not. (hence checking if the value (client-ID, e.g. "012345-6789") in Cell I5 is present in Range I7:I5000)

Now, what I want to do is to "move" this into the macro code, so I don't need to have the formula on the sheet.

The VBA code I have now is:

Dim myCheck As Integer

myCheck [B]= ActiveSheet.Cells(4, 10)[/B]
    
If myCheck = 0 Then

I.e I want to replace the code in bold with the VBA equivalent of the cell function above.

Any thoughts?

 

[shg]

Does the client ID appear in isolation in the range, or is it contained in some longer string?

What's the objective in converting the formula to a UDF?

 

[Lallo]

Does the client ID appear in isolation in the range, or is it contained in some longer string?


What's the objective in converting the formula to a UDF?

Thanks for taking the time to answer!

The client ID appears in isolation, one per cell, yes.

The objective for UDF is twofold.

1) I will soon have people even more programming illiterate than me enter clients to the list, and I fear if I leave anything in there that is nor crystal clear - they will mess it up - seen it happen before. Now this could probably be solved by placing the cell elsewhere and hiding it, which brings me to reason 2:

2) Being a quasi-noob @ VBA programming, I realised I didn't know how to do this, but want to - trying to learn... a reason as good as any in my book.

As for the object of the whole exercise - we receive client lists on paper from teaching seminars, and need to report how many hours each client (individual) receives every month, and during the whole project.

 

[Lallo]

[