Count characters between two empty space to dashes() in vba

Question

How do I get the length of character between beginning with space and ending with * Here is the image. Column B shows the total len before dasher(-) and my code

Sub xn()
    Dim x As Integer
    x = 1
    If Worksheet("Sheet1").Range("A"& x).len(Right," ") Or _
    Worksheet("Sheet1").Range("A"&x)len(Left,"-") Then
        len(totallen)
    End If
    x = x + 1
End Sub

Answer 1

The code posted has multiple issues:

1. Worksheet is not a valid object - you need to use Worksheets.
2. .len is not a property of a Range object.
3. Even in .len was a property of a Range, you would need a de-reference operator (aka '.') in here: Range("A"&x)len(Left,"-")
4. If you intend to use the function Len(), it only takes one argument.
5. You apparently are trying to loop, but you need to use either a For or For Each loop - it won't loop automatically when you increment x at the bottom of the sub.
6. Right is a function, but you're calling it without arguments and they are not optional.
7. Similarly, Left is a function, but you're also calling it without the required arguments.
8. totallen is not declared anywhere, so Len(totallen) will assume that totallen is a Variant (default for undeclared variables), then cast it to a String, and then always return 0 because it has never been given a value.
9. Anything else I may have missed.

The solution is to use the InStr function. It returns the location in a string of a given sub-string.

Sub xn()

    Dim x As Long
    Dim sheet As Worksheet

    Set sheet = ActiveWorkbook.Worksheets("Sheet1")
    For x = 1 To sheet.Range("A" & sheet.Rows.Count).End(xlUp).Row
        sheet.Cells(x, 2) = InStr(1, sheet.Cells(x, 1), "-") - 1
    Next x

End Sub

I'd also recommend taking a look at the MSDN article on Looping Through a Range of Cells (2003 vintage, but still valid), and Error Finding Last Used cell In VBA.