如果单元格包含3个特定字符，请删除这些字符，否则添加这些字符

Sub Test()
Dim c As Range
Dim d As Range

If InStr(1, c, "+") Then
For Each c In Selection
c.Replace What:="(", Replacement:="", LookAt:=xlPart, SearchOrder _
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
c.Replace What:=")", Replacement:="", LookAt:=xlPart, SearchOrder _
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
c.Replace What:="+", Replacement:="", LookAt:=xlPart, SearchOrder _
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next c
End If

For Each c In Selection
If c.Value <> "" Then c.Value = "+" & c.Value
c.Replace " ", " +"
Next

For Each d In Selection
If d.Value <> "" Then d.Value = "(" & d.Value & ")"
Next d
End Sub

I am trying to create a toggle since my Add-In will not show and I cannot seem to figure out how to place it in the Add-In screen. Searched high and low and others seem to have the same issue. So, besides that wonderful problem, I cannot seem to figure out how to create a toggle with the button that somehow made it to the Add-In.

我正在尝试创建一个切换,因为我的加载项不会显示,我似乎无法弄清楚如何将它放在加载项屏幕中。搜索高低,其他似乎有相同的问题。所以,除了这个奇妙的问题,我似乎无法弄清楚如何创建一个切换按钮,以某种方式使其成为加载项。

All I want is to say:

我只想说:

If you find "+" or "(" or ")" then remove those characters from the cells otherwise if you DO NOT find those characters in the cell, add them at specific places. So this should be the scenario:

如果找到“+”或“(”或“)”,则从单元格中删除这些字符,否则如果您未在单元格中找到这些字符,请将它们添加到特定位置。所以这应该是场景:

Original String: Hello World

原始字符串:Hello World
Click Once: (+Hello +World)
Click Twice: Hello World

Please help I am going crazy here.

请帮助我在这里疯狂。

Thank you all!

谢谢你们!

2 个解决方案

#1

I think you just misplaced your End If so every time you were addind the characters, even if they were already there in the first place!

我认为你错了你的结局如果是的话,每当你加入角色时,即使他们已经在那里了!

And you also misplaced the For each c , therefore "c" wasn't set and couldn't be used!

你也错放了For each c,因此“c”没有设置也无法使用!

Take the habit of incrementing your code so it's more readable, you won't have that kind of problem! ;)

养成增加代码的习惯,使其更具可读性,你就不会有这样的问题! ;)

Give this a try (I added the other characters tests) :

尝试一下(我添加了其他字符测试):

    Sub Test()
Dim c As Range

For Each c In Selection
    If InStr(1, c, "+") Or InStr(1, c, "(") Or InStr(1, c, ")") Then
        c.Replace What:="(", Replacement:="", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        c.Replace What:=")", Replacement:="", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        c.Replace What:="+", Replacement:="", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

    Else
            If c.Value <> "" Then c.Value = "+" & c.Value
            c.Replace " ", " +"

            If c.Value <> "" Then c.Value = "(" & c.Value & ")"
    End If
Next c
End Sub

#2

c is not yet defined when you first try to run a function on its value.

首次尝试对其值运行函数时,尚未定义c。

Sub Test()
Dim c As Range
Dim d As Range

If InStr(1, c, "+") Then
For Each c In Selection

Tested, working variant, somewhat optimised too.

经过测试的工作变体,也有所优化。

Sub Test()
Dim c As Range

For Each c In Selection
    If InStr(1, c, "+") Then 'if it has a plus sign
        c.Value2 = Replace(Replace(Replace(c.Value2, "(", ""), ")", ""), "+", "") 'replaces (, ) and +
        Else 'if it doesn't have a plus sign
        If c.Value2 <> "" Then 'if it's not zero length
            c.Value2 = "(+" & Replace(c.Value2, " ", " +") & ")" 'replaces " " with " +" and puts the value in brackets, after the first bracket comes a plus sign as per your example
        End If
    End If
Next c
End Sub

#1