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.I had to translate the formulas so Find may not be 100% accurate, as in my language its something like "x.Find".=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 came up with this code, but it doesnt work at all.
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.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