我之前有一些符号需要空间和在excel VBA之后 [英] I have some symbols need space before & after in excel VBA

问题描述

我一直在寻找几天的答案，但还没有。

如果有任何大写或小文本和数字，那么下面的空间要求。 VBA宏仅运行匹配大小写[A。 &安培;一个 。 &安培; 1。]应该是[A. &安培;一个。 &安培; 1.]删除空间

I have been searching for the answer to few days, but nothing yet.
In case any capital or small text and number are then space requirement below there. VBA macro runs only match case only [A . & a . & 1 .] should be [A. & a. & 1.] removing space

1	. 	 DOT	              No Space Before & After Single Space
2	: 	 Colon	              No Space Before & After Single Space
3	, 	 Comma	              No Space Before & After Single Space
4	( 	 Opening Parenthesis  Before Single Space & No Space After
5	) 	 Closing Parenthesis  No Space Before & After Single Space
6	/ 	 Slash	              No Space Before & After
7	- 	 Hyphen	              Before & After Single Space
8	" 	 Left double quote    Before Single Space & No Space After
9	" 	 Right double quote   No Space Before & After Single Space
10	! 	 Exclamation point    No Space Before & After
11	# 	 Number sign	      No Space Before & After
12	* 	 Asterisk	      No Space Before & After
13	; 	 Semicolon	      No Space Before & After Single Space
14	_ 	 Underscore	      No Space Before & After
15	{ 	 Opening Brace	      Before Single Space & No Space After
16	} 	 Closing Brace	      No Space Before & After Single Space
17	‘ 	 Left Single Quote    No Space Before & After
18	’ 	 Right Single Quote   No Space Before & After

如果2个符号冲突，那么我们有VBA宏。



我试过的：



我已开始编码：

In case 2 symbols Conflict then we have VBA macro.

What I have tried:

I have start coding:

Sub Multi_FindReplace()

Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As Variant
Dim x As Long

fndList = Array(":.", ",.")
rplcList = Array(": .", ", .")

'but there is limits of replacing only 50


For x = LBound(fndList) To UBound(fndList)
For Each sht In ActiveWorkbook.Worksheets
sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next sht

Next x

End Sub

推荐答案

我这样做是为了练习。我不相信我的方法特别好，它可能需要一些时间来处理，但是这里... ...



我设置了几个数组。一个持有可能受影响的符号，另一个持有您问题中列出的规则。每个数组的每个元素的索引必须匹配。

I did this as an exercise. I'm not convinced that my approach is particularly good and it could potentially take some time to process but here goes...

I set up a couple of arrays. One holds the symbol that could be affected and the other holds the rules as laid out in your question. The indexes of each element of each array must match up.

Dim arrSymbols(19) As String
Dim arrCorrect(19) As String
Public Sub SetUp()
    arrSymbols(1) = ".": arrCorrect(1) = ". "
    arrSymbols(2) = ":": arrCorrect(2) = ": "
    arrSymbols(3) = ",": arrCorrect(3) = ", "
    arrSymbols(4) = "(": arrCorrect(4) = " ("
    arrSymbols(5) = ")": arrCorrect(5) = ") "
    arrSymbols(6) = "/": arrCorrect(6) = "/"
    arrSymbols(7) = "-": arrCorrect(7) = " - "
    arrSymbols(8) = "!": arrCorrect(8) = "!"
    arrSymbols(9) = "#": arrCorrect(9) = "#"
    arrSymbols(10) = "*": arrCorrect(10) = "*"
    arrSymbols(11) = ";": arrCorrect(11) = "; "
    arrSymbols(12) = "_": arrCorrect(12) = "_"
    arrSymbols(13) = "{": arrCorrect(13) = " {"
    arrSymbols(14) = "}": arrCorrect(14) = "} "
    arrSymbols(15) = "'": arrCorrect(15) = "'"
    arrSymbols(16) = Chr

（ 145 ）：arrCorrect（ 16 ）= Chr

(145): arrCorrect(16) = Chr

（ 145 ）' 左单引号
arrSymbols（ 17 ）= Chr

(145) 'Left single quote arrSymbols(17) = Chr

这篇关于我之前有一些符号需要空间和在excel VBA之后的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！