Error In Text Manipulation Formula

[Ark68]

I am having a difficult resolving the error in this code:

For pp = lRowst To lRowed
    .Range("Q" & pp) = Application.Left(.Range("B" & pp), (Application.Find("-", .Range("B" & pp) - 1)))
Next pp

I am getting a 'Wrong number of arguments or invalid property assignment.' error with the highlighted term.

Source:
B&pp (B24) = 9:00A-9:30A

Result:
Q&pp (Q24 = 9:00A

 

[VoG]

Try

    .Range("Q" & pp).Value = Left(.Range("B" & pp).Value, InStr(.Range("B" & pp).Value, "-") - 1)

 

[s hal]

I believe the issue is where your -1 falls in your Find() function. Do you maybe want B"pp-1" instead of the cell B"pp" - 1?

 

[Ark68]

Hi Peter, yes ... that was what I needed.
Always appreciate you help.

 

[Ark68]

Thanks s hal for your reply. Appreciated!

This procedure has added another hurdle. The value this formula creates is text and being so, does not allow me to to sort by time.
What addition do I need to make that text string into a usable time value? The value doesn't need any specific formula, it just has to be a valid time value for sorting.

 

[VoG]

Try

.Range("Q" & pp).Value = TimeValue(Left(.Range("B" & pp).Value, InStr(.Range("B" & pp).Value, "-") - 1))

 

[Rick Rothstein]

For pp = lRowst To lRowed
    .Range("Q" & pp) = Application.Left(.Range("B" & pp), (Application.Find("-", .Range("B" & pp) - 1)))
Next pp

You can do what you intended with the above code much faster without using a loop at all (I also added statements to make your text values into time values)...

With .Range("Q" & lRowst & ":Q" & lRowed)
  .Value = .Range("B" & lRowst & ":B" & lRowed).Value
  .Replace "-*", "", xlPart
  .Replace "A", " A", xlPart
  .Replace "P", " P", xlPart
End With

This With/End.With block would be nested inside the With/End.With block that the "dots" in your original code refer back to.

 

[Ark68]

That was quick ... and painless. Thanks again Peter.

 

[Ark68]

Hi Rick ... thanks for sharing your method. Certainly something for me to digest as an educational opportunity. I'm amazed of how many different ways there are to do things.