CODEWITHSUNDEEP
vbaexcel
xyz
xyz

Reputation: 2300

Code to remove everything to the right of a char givess error

I am tiring to remove everything including the . to the right of the . e.g aaaaa.ntuthuh I get aaaaa

If I paste = Left(A1, Find(".", A1) - 1) in a cell in the worksheet the formula works fine but in a macro I get the errorSub or Function not defined and the Find is highlighted

Thanks

Sub Split()
    Dim fName As String

    fName = Left(A1, Find(".", A1) - 1)
    ActiveSheet.Range("B1").Value = fName

    ' have also tyred: Range("A1").Value = "=Left(A1, Find(".", A1) - 1)"  and: Range("A2").Formula = "=Left(A1, Find(".", A1) - 1)" but throws error "." is highlighted a "Expected end of statement"

End Sub

Upvotes: 1

Views: 69

Answers (3)

APrough
APrough

Reputation: 2701

Your code should work, with one minor change. Before, you had in a value of A1 for the Left and Find functions, when you should be supplying the range to calculate. Note that this may cause errors if there is no "." in the input string.

Sub Split()
    Dim fName As String

    fName = Left(activesheet.Range("A1"), Find(".", ActiveSheet.Range("A1")) - 1)
    ActiveSheet.Range("B1").Value = fName
End Sub

However, a different solution would be to create a function that allows you to specify the range you want stripped, and to output the result. So, in cell B1, you would put "=Splitter(A1)"

    Function Splitter(range As range) As String
        Dim fName As String
        If InStr(range.Value, (".")) = 0 Then
            Splitter = range.Value
        Else
            fName = Left(range.Value, InStr(range.Value, ".") - 1)
            Splitter = fName
        End If
    End Function

Upvotes: 2

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35863

There are two options for you:

1) use .Formula property

With ActiveSheet.Range("B1")
    .Formula = "=Left(A1, Find(""."", A1) - 1)"
    .Value = .Value
End With

2) use Evaluate:

fName = Evaluate("Left(A1, Find(""."", A1) - 1)")
ActiveSheet.Range("B1").Value = fName

both will give you the same result

Upvotes: 2

Gary's Student
Gary's Student

Reputation: 96791

And here is a third option:

Sub Splitt()
    Dim fName As String
    fName = Split(Range("A1").Value, ".")(0)
    ActiveSheet.Range("B1").Value = fName
End Sub

Upvotes: 2

Related Questions