6

I have written the following code to remove text after '*' or '-' characters in one of the rows using VBA in Excel but it's giving an error.

Sub Removetext()

For Each c In Range("A1:ZZ1")
    c.Value = Left(c.Value, InStr(c.Value, "-") - 1)
Next C

End Sub

How can I remove the text after these characters?

jordanz
  • 367
  • 4
  • 12
user3899966
  • 73
  • 1
  • 2
  • 4
  • 1
    I'm sure someone could help. Why don't you edit your question to include information such as the error you are receiving; sample data from your input range; and your expected output? Also, please read the HELP topics for [How do I Ask a Good Question](http://stackoverflow.com/help/how-to-ask), and also [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve) – Ron Rosenfeld May 18 '16 at 21:02
  • 2
    Add `If InStr(c, "-") > 0 Then` before your `c.value`. – findwindow May 18 '16 at 21:03
  • Essentially your `InStr(c.Value, "-")` is returning a `0` or `-1` when it doesn't find a `-` in your string. And `0 - 1 = -1`, and you can't take the left most -1 characters of a string. – JNevill May 18 '16 at 21:05

1 Answers1

6

As it has been said in the comments of @JNevill and @fidnwindow, you need to test whether the object of your search is found or not:

Sub Removetext()

For Each c In Range("A1:ZZ1")
    If InStr(c.Value, "-") > 0 Then
        c.Value = Left(c.Value, InStr(c.Value, "-") - 1)
    End If
        If InStr(c.Value, "*") > 0 Then
        c.Value = Left(c.Value, InStr(c.Value, "*") - 1)
    End If
Next c

End Sub

The issue is that when InStr does not find the criteria it returns 0. So now you are looking for the Left -1 characters which will throw and error.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • 2
    just as a hint: there is no need for the `> 0`... however, most ppl don't like it that way :P – Dirk Reichel May 18 '16 at 21:06
  • 1
    @DirkReichel So true. I did not want to explain the Boolean state of TRUE/FALSE. In my own code I would have truncated it. But a great point. – Scott Craner May 18 '16 at 21:08
  • 1
    @DirkReichel - Thanks for pointing that out, I always forget you can do that. Using the formula without the `>0` makes me look more smarter, so I'm going to use it more often! :P – BruceWayne May 18 '16 at 21:14
  • @user3899966 please mark as correct, by clicking the check mark by the answer. It is something only you can do. It will close the question as answered and remove it from the unanswered queue. – Scott Craner May 18 '16 at 21:20
  • Hi, I am new to VBA and would like to write a Code to open file dialogue box to import another excel file data with same worksheet name as source worksheet, same data format as source, run the macro, and save the new file with new name. advance thanks to help on this need Thanks a lot. – user3899966 May 18 '16 at 21:24
  • 1
    @user3899966 that is a new question and deserves its own post, but I suggest first using the macro recorder and trying to clean it up. When you get stuck come back and post the code and the problem and we will be here to help. – Scott Craner May 18 '16 at 21:26
  • 2
    @BruceWayne it only looks smart if no one can read it anymore... try to deal a lot with `[ranges]` and `:` like this: `Sub Removetext(): Dim c: For Each c In [A1:ZZ1]: If InStr(c.Value, "-") + InStr(c.Value, "*") Then c.Value = Left(c.Value, InStr(Replace(c.Value, "-", "*"), "*") - 1): Next: End Sub` (it needs to be just one line) :D – Dirk Reichel May 18 '16 at 21:54
  • @DirkReichel - I also recently learned you can use [`Let`](https://msdn.microsoft.com/en-us/library/bb531379.aspx) in VBA, which is usually implicit, but I think adding it will make me look even **smarter** :D (haha, thanks for your post, got a nice laugh out of that). – BruceWayne May 18 '16 at 22:06
  • 1
    @BruceWayne [THIS](http://stackoverflow.com/questions/37147979/why-does-this-code-compile-when-pasted-in-but-fail-otherwise) made me feel really dumb. – Scott Craner May 19 '16 at 00:12