insert a SEARCH formula in VB

[jorgefilemon]

hi guys!


how do i write this in VB

=MID(A1,SEARCH(" ",A1,1)+1,SEARCH(" ",A1,SEARCH(" ",A1,1)+1)-SEARCH(" ",A1,1))


i want to have this formula inside my already created function SKU in VB, I really dont know any VB.




Function SKU(myEntry As String) As String


SKU = Left(myEntry, 3) & _
(THE FORMULA HERE!)
Mid(myEntry, InStr(InStr(myEntry, " ") + 1, myEntry, " ") + 1, 2) & _
Right(myEntry, 2)


End Function

 

[Worf]

Hi Jorge

Function SKU$(E$)
Dim s
s = Evaluate("=MID(A1,SEARCH("" "",A1,1)+1,SEARCH("" "",A1,SEARCH("" "",A1,1)+1)-SEARCH("" "",A1,1))")
SKU = Left(E, 3) & s & Mid(E, InStr(InStr(E, " ") + 1, E, " ") + 1, 2) & Right(E, 2)
End Function

 

[Worf]

or:

Function SKU$(E$)
SKU = Left(E, 3) & _
Evaluate("=MID(A1,SEARCH("" "",A1,1)+1,SEARCH("" "",A1,SEARCH("" "",A1,1)+1)-SEARCH("" "",A1,1))") & _
Mid(E, InStr(InStr(E, " ") + 1, E, " ") + 1, 2) & Right(E, 2)
End Function

 

[jorgefilemon]

hey Worf, thanks for taking ur time and answering my question!, it doesn't quiet work though.

I don't want A1 cell actually, i substituted A1 for the variable E based on ur code, but still didn't work.

i'm only missing this part in my code, the ability to take the whole number from second position in my description column regardless the length of it.

i uploaded a pic, maybe it's clearer this way

 

[Worf]

This version gets the information from the cell located two columns apart the function cell:

Function SKU$(E$)
Dim a
a = Range(Application.Caller.Address).Offset(, 2).Address               ' two columns to the right
SKU = Left(E, 3) & Evaluate("=MID(" & a & ",SEARCH("" ""," & a & ",1)+1,SEARCH("" ""," & _
a & ",SEARCH("" ""," & a & ",1)+1)-SEARCH("" ""," & a & ",1))") & _
Mid(E, InStr(InStr(E, " ") + 1, E, " ") + 1, 2) & Right(E, 2)
End Function

 

[jorgefilemon]

hi worf, thanks a lot again!!!.

the code is making a space in the sku(clave) that I don't need though.

that's the only part missing, if you could help me to fix it.. i'll appreciate it tons

This version gets the information from the cell located two columns apart the function cell:

Function SKU$(E$)
Dim a
a = Range(Application.Caller.Address).Offset(, 2).Address               ' two columns to the right
SKU = Left(E, 3) & Evaluate("=MID(" & a & ",SEARCH("" ""," & a & ",1)+1,SEARCH("" ""," & _
a & ",SEARCH("" ""," & a & ",1)+1)-SEARCH("" ""," & a & ",1))") & _
Mid(E, InStr(InStr(E, " ") + 1, E, " ") + 1, 2) & Right(E, 2)
End Function

 

[Worf]

Please test this:

Function SKU$(E$)
Dim a$
a = Range(Application.Caller.Address).Offset(, 2).Address               ' two columns to the right
SKU = Replace(Left(E, 3) & Evaluate("=MID(" & a & ",SEARCH("" ""," & a & ",1)+1,SEARCH("" ""," & _
a & ",SEARCH("" ""," & a & ",1)+1)-SEARCH("" ""," & a & ",1))") & _
Mid(E, InStr(InStr(E, " ") + 1, E, " ") + 1, 2) & Right(E, 2), " ", "")
End Function