VBA Equivalent for Formula

[Raymond_India]

Dear friends,

I have typed the value "200-250" in cell B3.

On cell D3 I type the formula =LEFT(B3,FIND("-",B3-1) and it returns the value 200
On cell E3 I type the formula =RIGHT(B3),LEN(B3)FIND("-",B3) and it returns the value 250
This works perfectly well with formulas.
Please can someone help me with the VBA equivalent for the same thing ?

Regards
Raymond

 

[Brombrough]

Hi Raymond,

Try

the formula for the cell to get the first value:-

=getValueFirstOrLast(B3,"F")

the formula for the cell to get the Last value:-

=getValueFirstOrLast(B3,"L")

Public Function getValueFirstOrLast(varCellvalue As Variant, varType As Variant) As Long

'check if '-' is contained with cell


If InStr(1, varCellvalue, "-") = 0 Then
' '-' not found return 0
getValueFirstOrLast = 0
Exit Function
End If

If varType = "F" Then
getValueFirstOrLast = Trim(Left(varCellvalue, InStr(1, varCellvalue, "-") - 1))
ElseIf varType = "L" Then
getValueFirstOrLast = Trim(Mid(varCellvalue, InStr(1, varCellvalue, "-") + 1))
Else
' First or last not included so return 0
getValueFirstOrLast = 0
End If
End Function


Hope this helps

 

[steve the fish]

Im not sure how you want to use it but you could use split:

x = Split(Range("B3"), "-")(0)
y = Split(Range("B3"), "-")(1)