Making a function based on Find having IF

[asktheages]

Hello!

Im trying to make a function that gets the value from certain cell (for example: S-1140M), and if other cell has value "X" it searches for the value in sheet3 columns C:C and gives Offset(0,2) result, if no "X" then it searches columns L:L in sheet3 and also gives Offset(0,2) result.

Its doable in Excel, but the formula is very long and not elegant at all.

=If($D$2="X";FIND(I9;'CENNIK CZĘŚCI'!C:C;'CENNIK CZĘŚCI'!E:E;0;1;1);FIND(I9;'CENNIK CZĘŚCI'!L:L;'CENNIK CZĘŚCI'!N:N;0;1;1))

I had to translate the formulas so Find may not be 100% accurate, as in my language its something like "x.Find".

I came up with this code, but it doesnt work at all.

Function Searchy(Value As Range) As Integer
Dim First As Range
Dim Second As Range
Set First = Workbooks("3_Wycena").Worksheets("CENNIK CZĘŚCI").Range("C:C").Find(Range(Value).Offset(0, 2).Value)
Set Second = Workbooks("3_Wycena").Worksheets("CENNIK CZĘŚCI").Range("L:L").Find(Range(Value).Offset(0, 2).Value)
If Range("D1").Value = "X" Then
  Searchy = First
Else
  Searchy = Second
End If
End Function

Could you please help me? Im pretty new to VBA, i could do it in Sub procedure, but i need Function. Its definitely doable, since Excel built-in formulas allow me to do it.

[georgiboy]

Welcome to the forum!

How about the function on the attached?

[Aussiebear]

G'day asktheages, Welcome to the vbaexpress excel forum.

Excel's Find function is normally used to "find" a substring with a string. So we will need to replace that with something else.


Never mind Georgiboy has done the work whilst I was on the phone

[asktheages]

Welcome to the forum!

How about the function on the attached?

This!!! Works perfectly well, thank you!!