asktheages
11-23-2022, 02:54 AM
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.
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.