CODEWITHSUNDEEP
vbaexcel
AddyProg
AddyProg

Reputation: 3050

Removing A part of Cell value in VBA

I am trying to remove a part from cell values but its not working.

Sample content of a cell:(Sample Input)

="*sls*lmt"&CONCATENATE("_",'C:\Users\Administrator\AppData\Roaming\Microsoft\AddIns\XL-EZ Addin.xla'!COLUMNLETTER(C14),TEXT(ROW(),"0"))&"_sel*range(status)*0*GOOD|OK|0|#00D700|0|100|80*NOT GOOD|NG|0|#FFFF00|0|79|50*FAULTED|FAULTED|0|#FF0000|1|49|0"

Sample Output:

="*sls*lmt"&CONCATENATE("_",!COLUMNLETTER(C14),TEXT(ROW(),"0"))&"_sel*range(status)*0*GOOD|OK|0|#00D700|0|100|80*NOT GOOD|NG|0|#FFFF00|0|79|50*FAULTED|FAULTED|0|#FF0000|1|49|0"

and I want to remove this part from value

'C:\Users\Administrator\AppData\Roaming\Microsoft\AddIns\XL-EZ Addin.xla'

Here is what I have tried

Sub UpdateSheetButton()
Dim subStr1 As String
Dim subSrrt2 As String

Dim pos As Integer

For Each Cell In ActiveSheet.UsedRange.Cells
pos = InStr(Cell.Value, "'C:\")
If pos >= 1 Then
  subStr1 = Split(Cell.Value, "'C:\", 1)
  subStr2 = Split(Cell.Value, "\AddIns\XL-EZ Addin.xla'", 2)
  Cell.Value=subStr1 +subStr2 
  End If
Next

End Sub

Error: Runtime error '13' Type Mismatch on this line 

pos = InStr(Cell.Value, "'C:\")

Upvotes: 1

Views: 4951

Answers (3)

Jean-François Corbett
Jean-François Corbett

Reputation: 38500

The cause of your Type Mismatch error is this: Split returns an array, but you're assigning it to a String. So you need to declare your variables like this:

Dim subStr1() As String
Dim subStr2() As String ' caution: there is a typo here in your code

Also, Split is a bit clumsy to work with. I'd go for string functions like InStr and Mid instead.

Moreover, If I understand correctly, you're trying to modify the cell's formula. However, your existing code looks at the cell's value. Not the same thing. Change .Value to .Formula.

Finally, your sample output is not a valid formula. Get rid of the ! before COLUMNLETTER:

="*sls*lmt"&CONCATENATE("_",!COLUMNLETTER(C14),TEXT(ROW(),"0"))&"_sel*range(status)*0*GOOD|OK|0|#00D700|0|100|80*NOT GOOD|NG|0|#FFFF00|0|79|50*FAULTED|FAULTED|0|#FF0000|1|49|0"
                            ^
                          no good

With all this in mind, a better way to accomplish your task is this:

Function RemoveTextBetween(ByVal strInput As String, _
    ByVal strStartCut As String, ByVal strEndCut As String) As String

    Dim iStartCut As Long
    Dim iEndCut As Long
    iStartCut = InStr(strInput, strStartCut)
    iEndCut = InStr(strInput, strEndCut) + Len(strEndCut)
    RemoveTextBetween = Left(strInput, iStartCut - 1) & Mid(strInput, iEndCut)
End Function

Example usage:

cell.Formula = RemoveTextBetween(cell.Formula, "'C:\", "\AddIns\XL-EZ Addin.xla'!")

Note how I added ! at the end to make sure it gets cut out.

Upvotes: 2

Siddharth Rout
Siddharth Rout

Reputation: 149297

I wonder why are you using VBA for such a simple task. You can use the inbuilt method.

WAY One (NON VBA)

Press CTRL + H to bring the Find and Replace Dialog Box and use that to replace the text.

WAY Two (VBA)

If you still want to use VBA then simply record a macro for the above. There is no need to use a loop. For example

Sub Sample()
    Dim sFind As String
    Dim sNew As String

    '~~> Example. Chnage as applicable
    sFind = "Sum"
    sNew = "Max" 'or sNew = "" in case you want to remove the above text

    '~~> For Complete Worksheet
    With ThisWorkbook.Sheets("Sheet1").Cells
        .Replace What:=sFind, Replacement:=sNew, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    End With

    '~~> For a Column
    With ThisWorkbook.Sheets("Sheet1").Columns(1)
        .Replace What:=sFind, Replacement:=sNew, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    End With

    '~~> For a range
    With ThisWorkbook.Sheets("Sheet1").Range("A1:A10")
        .Replace What:=sFind, Replacement:=sNew, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    End With
End Sub

Upvotes: 6

citizenkong
citizenkong

Reputation: 679

You can use Replace to substitute text in the cell's Formula property, i.e.

A1    B1    C1    D1    E1
 a     b     c     d     =CONCATENATE(A1,B1,C1,D1,"Something Else")

Let's say I want to remove the "Something Else" from the formula:

Sub removefromformula()

    Dim r As Range
    Set r = Me.[E1]

    Debug.Print r.Formula

    r.Formula = Replace(r.Formula, ",""Something Else""", "")

    Debug.Print r.Formula

End Sub

This returns:

=CONCATENATE(A1,B1,C1,D1,"Something Else")
=CONCATENATE(A1,B1,C1,D1)

In the immediate window.

Upvotes: 0

Related Questions