Instr runtime error

[welchs101]

I have this string which contains a hyphen "-". If i run the following code such that my temp_string has a "-" in it ........code will run fine. However, if i remove the "-" from the temp_string then the code has a run time error of "Invalid procedure call or argument".

I know that the reason i get this run time error is that i am looking for a hyphen in a string that has no hyphen ...........not sure what to do. Need some way of making program not "stop" ......not sure.

'temp_string = "Denosumab - Bone Loss Oncology"
temp_string = "Denosumab"

junk1 = InStr(1, temp_string, "-", 1) - 1

junk2 = Left(temp_string, junk1)

junk3 = Trim(junk2)

MsgBox (junk1)
MsgBox (junk2)
MsgBox (junk3)

[welchs101]

ook, i must be getting tired. I just realized that i shoudl just check to see if the "-" is in the string before i try and create a new string.

[tigeravatar]

welchs101,

What the code is trying to do is get all text to the left of the hyphen, then remove leading/ending spaces. If there is no dash, there's no text, so when it tries to get text to the left of the dash you get an error. Try this instead:

Sub testpart()
    
    Dim temp_string As String, clean_string As String
    
    'temp_string = "Denosumab - Bone Loss Oncology"
    temp_string = "Denosumab"
    
    If InStr(temp_string, "-") > 0 Then
        clean_string = Trim(Left(temp_string, InStr(temp_string, "-") - 1))
        MsgBox clean_string
    Else
        MsgBox "No hyphen found."
    End If
    
End Sub

Hope that helps,
~tigeravatar

EDIT: Looks like you got it before I replied

[welchs101]

Hi thanks for the info. I was kinda headed that way.

I did have another question. See enclosed file.

I am trying to parse all the values in col-A of sheet1. For some reason when you run this code (again see enclosed macro) when it reaches a string with a "-" in it ......code just seems to end like it finished.

Dim myinputwrksht As Worksheet
Dim new_prod_name As String
Dim junk1 As String
Set myinputwrksht = Workbooks("Parsing_product_name.xlsm").Worksheets("Sheet1")
last_row3 = 288

done = False
x = 2
Do While Not done
    MsgBox (x)
    If InStr(1, myinputwrksht.Range("A" & x).Value, "-", 1) > 0 Then
        MsgBox ("in this compare thing")
        myinputwrksht.Range("A" & x).Value = Trim(Left(myinputwrksht.Range("A" & x).Value, InStr(1, myinputwrksht.Range("A" & x).Value, "-", 1) - 1))
    End If
    x = x + 1
    If x > last_row3 Then
    done = True
    MsgBox ("done is true")
    End If
Loop

[tigeravatar]

welchs101,

Give this a try instead:

Sub CleanTextMacro_for_welchs101()
    
    Dim ws As Worksheet: Set ws = ActiveWorkbook.ActiveSheet
    Dim RowIndex As Long, ProductName As String
    For RowIndex = 2 To ws.Range("A" & Rows.Count).End(xlUp).Row
        If InStr(ws.Range("A" & RowIndex).Value, "-") > 0 Then
            ProductName = ws.Range("A" & RowIndex).Value
            ws.Range("A" & RowIndex).Value = Trim(Left(ProductName, InStr(1, ProductName, "-", 1) - 1))
        End If
    Next
    
End Sub

Hope that helps,
~tigeravatar

[welchs101]

I closed excel and then restarted it.............re-ran the macro AND IT RAN!!!!!!!!!!!!!

Any ideas why closing down excel and restarting would make it run?