Reputation: 2300
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
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
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
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