UDF - How to write a CONTAINS function in VBA

[Sphinx404]

Experts, I need you... I have no idea what I'm doing here.

Sheet1 Column A = Production #
Sheet 2 Column B = Kit #
Sheet2 Column A = Production #
Sheet2 Column B = Kit # (multiple values in single cell)

I need to lookup the production # in sheet 1, match it against the production # in sheet2, then if it's found, I need see if the Kit # in Sheet1 Col. B is found in Sheet2 Col. B

=IF Sheet1!A2 is found in Sheet2!A:A then lookup value Sheet1!B2 in the cell next to the production# (Sheet2!B:?), if value from Sheet1!B2 is found, then say "found", 0)

Does anyone have an idea on how I can accomplish this... either in Excel or VBA?

Thanks!

 

[Fluff]

How about

Function GetKit(Rng As Range)
   Dim fnd As Range
   GetKit = 0
   Set fnd = Sheets("new").Range("A:A").Find(Rng.Value)
   If Not fnd Is Nothing Then
      If InStr(1, fnd.Offset(, 1), Rng.Offset(, 1), vbTextCompare) > 0 Then GetKit = "Found"
   End If
End Function

 

[Sphinx404]

Thank you for responding Fluff. It is appreciated.

I was able to run a function that matched the Production #s and brought both Kit #s over to my main sheet ("AQE Data")

Now I have the KIT # I'm looking for in Column R, and the array of Kit's in Column S. Now I just need to compare or ask Excel (via your function) to look at the data.

Is column R (single value) found in column S (multiple values)

I apologize for my ignorance, but I don't even know how to decipher your function to make this work.

Thanks again.

 

[Fluff]

What sort of values do you have for the Kits?

 

[Sphinx404]

Example:

Column R
313A
319c
311D
357B
312B
297d
317a
315A

Column S
343b, 354b
341A,358A
358
358c, 362, 345a, 341B
336b, 359D
317a,297D, 357B

 

[Fluff]

In that case try

Function GetKit(Rng As Range)
   Dim fnd As Range
   Application.Volatile
   GetKit = 0
   If InStr(1, Rng.Offset(, 1), Rng, vbTextCompare) > 0 Then GetKit = "Found"
End Function

In a blank cell type =GetKit(R13)

 

[Sphinx404]

You know when you create a UDF in VB? When you type in your parameter... in this case "GetKit" or more specifically "=GetKit"... nothing comes up... like it cannot find the function. I'm not expert, but shouldn't a UDF come up automatically when you start typing it in... (i.e. =GetKit.... and as you type it auto-populates for you to select?)

I type in =GetKit and Excel doesn't recognize the function. So I type it in anyway and all I get is #NAME ?

I typed it in just as you stated in your previous reply.

 

[Fluff]

The function needs to go in a regular module. I suspect that you have put it in a sheet module, or the ThisWorkbook module

 

[Sphinx404]

Ahh, yes of course. Perfect. Works perfectly, thank you Fluff!

 

[Fluff]

Glad to help & thanks for the feedback