运行时间错误:5无效的过程调用或参数在VBA
Private Sub btnsubmit_Click()
Dim Msg As String
Dim pos1 As Integer
Dim pos2 As Integer
Dim Count As Integer
Dim flag As Integer
Dim telphno
Msg = TextBox1.Value
pos1 = 1
pos2 = 1
flag = 0
Do While pos1 < Len(Msg)
pos1 = InStr(pos1, Msg, "[")
If flag = 0 Then
pos2 = InStr(pos2, Msg, "]")
End If
If pos2 - pos1 < 5 Then
ActiveCell.Value = Mid(Msg, pos1 + 1, pos2 - pos1 - 1)
Count = 0
'Loop through the entire string
For i = pos2 To Len(Msg)
'Check to see if the character is a numeric one
If IsNumeric(Mid(Msg, i, 1)) Then
'Add it to the answer
telphno = telphno + Mid(Msg, i, 1)
Count = Count + 1
'Check to see if we have reached 10 digits
If Count = 10 Then Exit For
Else
telphno = ""
Count = 0
End If
Next i
ActiveCell.Offset(0, 1).Value = telphno
flag = 0
ActiveCell.Offset(1, 0).Select
pos1 = pos2 + 1
pos2 = pos1
telphno = ""
Else
flag = 1
pos1 = pos1 + 1
End If
Loop
End Sub
我想在我的字符串来获取“[”和“]”之间的字符,但是MID的功能是给我的错误。请帮我解决这个问题。
找到“[”和“]”之间的字符后,我正在寻找最近的10位数字。我正在通过TextBox(用户输入)获取字符串。 并在Excel工作表上打印输出。
为前
[A22]1239163332bcfhds[B23]6453jhddf2784637281ajdnjda[C33]dksamkd1288776655
对于上述字符串。它应该给 -
A22 1239163332
B23 2784637281
C33 1288776655
输入2 -
@fiJaeasafiGpaaaaod [A1] # 42, 5532 23156 “63’ 8:355 dedmaa @656 663336, {33538365. sail & as" 53666 wee—9008799499. @dfis.ée.maae06 [A2] S/O éegddad mweefi @dfiE aaseaefi mwaossae We" flees?) all 359% as" fidee, wee—9886557596. $036903: WI 29365 amass @aoflae mafia] (me) sail was?“ asllmairid’, aha—9945173528. @6066 60333305 2:056. [A4] £06333 messes $6503.91: 33:12:05 mews. 8.133133% a?" aiding as" mamas. aha-9886444737. [50%. memergfios, [A5] mew 89335365 mamas. mama. aan wage, as" eagdade, diam—9731742667. eaaiodfiaas. [A6] ge Wagfiegd mwaofisae mafia) £3966.mafia).sail mg"), as" mairifi, met—9986611558. @8396 30653236 [A7] fleas $839395 fleas# 4138/38, Sgéegd 33905:,623 finaSeag soomrf,ao.&.&.‘w’ea§ss,$3913.29. fideaas 6:312:36,anaemia.all dog as" macaw-36,mom—9448166197.mamas 8985305 [A8192 ageng mews sambaaadefiaefiéfieo.all adswsg, all warm,dam—[email protected] games [A9][email protected]&.dsaec56maisafid.aall $33.19 as" mamrid’,wag—9844644272.as.demam] 33mmadésada.Gall 836%62, as" $3668, wee:—8.839%:ngge [imam meme $36033“flaccid weave.mweafid.all 623; as" maiarid,dam—9481161243.agodaé.8.ao.129330556 agaossae53%;.a?" & as" 5365366,mez—[email protected]@[email protected]:330:36 @9033“we ems aim-.1353,swag.all 666663, as" maladdwag—8123565686.gang-56$60506 masses fiaofiaemafifleas mews,swag.all 8665383, as" mdwfid aha—9845781954.[A10][A11][A12][A13][A14]$63535 3:633:56 [A15]@663 aaaaaefi SawadJaeawe: aa£eas€ mews,{3368366.sail 6.9% all 0369966,dam—9945707587eaoadfi mesmereaa’cfiwsl6:36:38 agaoisaefiasfiewéédaall 303:3, as" 03653613,dam-9900436152.$830335eiedafi $885366 Qawaeeiapflsaecss,asasdne.aall wipe, as" 53566,Elma—9448218974.$69836 memergfios [A18]# 167,“&oa5555”15’ 2:336, 63:: @55,deg-353255 9:355 acme-3%,massarifi.aall 3.3.333 as” nae-36386,WEE—[email protected]%§ mewaergfics [A19]# 794/8 oomfieo.modes mews,65366666.all 6333 as" mwwfi'fi,dam—9945434802.$830535 Meme®$cs [A20]@30335 masses Mathewsmandaoddwsg.sail fine]; as" 53538363,[email protected] memergaos903361103: $358365 Saaawsae3536333.Ball 36335623, as" $368966,Wei-9972675782[A17] [A21]
预期输出继电器 -
A1 9008799499
A2 9886557596
A4 9886444737
A5 9731742667
A6 9986611558
A7 9448166197
并以此类推,直到
A20 9980170633
您的代码使用RegEx
“尖叫”,请参阅下面的代码(代码注释内容)。
子btnSubmit按钮代码
Option Explicit
Private Sub btnsubmit_Click()
Dim Msg As String
Dim pos1 As Integer
Dim pos2 As Integer
Dim posDelta As Integer
Dim telphno
Dim i As Integer
' added these 2 variables
Dim insideBrackets As String
Dim telphnoPos As Integer
TextBox1.Value = Sheets("sheet2").Range("H5").Value
Msg = TextBox1.Value
pos1 = 1
pos2 = 1
' loop while Msg still no emptied out
Do While (Msg) <> ""
TextBox1.Value = Msg
Debug.Print Len(Msg)
pos1 = InStr(Msg, "[")
pos2 = InStr(Msg, "]")
' find number of characters between "[" and "]"
posDelta = pos2 - pos1
Select Case posDelta
Case Is < 0 ' only "]" found , and no "["
Msg = Right(Msg, Len(Msg) - pos2)
Case 3, 4 ' could be A# , or A##
insideBrackets = Mid(Msg, pos1 + 1, pos2 - pos1 - 1)
telphno = "" ' reset value
telphnoPos = 0
Msg = Right(Msg, Len(Msg) - pos2)
' call function with Regex to find first 10 digits in string
telphno = GetFirstTenDigits(Msg)
' find position of first 10 digits inside the string
If telphno <> "" Then telphnoPos = InStr(Msg, telphno)
' successfult 10-digit resulted from RegEx
If telphnoPos > 0 Then
ActiveCell.Value = insideBrackets
ActiveCell.Offset(0, 1).Value = telphno
' remove characters from string that were extracted to the cells
Msg = Right(Msg, Len(Msg) - (telphnoPos + 10 - 1))
insideBrackets = "" ' reset value
' advance 1 row
ActiveCell.Offset(1, 0).Select
Else
Msg = "" ' no 10 digits ccurrences left
End If
Case Is > 4
Msg = Right(Msg, Len(Msg) - pos1)
End Select
Loop
End Sub
***Function GetFirstTenDigits* Code** (uses the `Regex` object)
Function GetFirstTenDigits(byMixedString As String) As String
' this function uses the RegEx to find all numeric characters insde the passed string
' then it searches for the first occorunce that the number of digits = 10 ,
' and returns it to the calling Sub
Dim RegEx As Object, Matches As Object, Match As Object
Set RegEx = CreateObject("vbscript.regexp")
With RegEx
.MultiLine = False
.Global = True
.IgnoreCase = True
.Pattern = "(\d+)" ' Match any set of digits
End With
Set Matches = RegEx.Execute(byMixedString)
For Each Match In Matches
If Len(Match) = 10 Then
' return the first match of 10 digits
GetFirstTenDigits = Match
Exit Function
End If
Next Match
End Function
下面是什么?你想测试什么输入?我没有包含在我的代码错误hanlding中,有错误处理的infinte选项。 –
为此输入获取相同的错误 - ésaeasmeme。 [A671 8353698个妈妈们AO音响米 FL OCKS马厩,03338366. 为 “= 3:一个为” maisedd, AHA-9731432004。 fi saeas 33.8)。 [A681 aesofnge imam Egao fi sae eadrboa。 球domais为 “年龄3, 凌晨-9449343829。 6353 $三十三万五千三百六十六AO FL我们[A69] 35393389655网络OE科幻克, 8:55 50135主题, 台面。 为” 坝, AHA-9448640505 。 [A70] 533气体agaoisae&aeaoisae 6355306 $ 3665,853333933, 65365366. 一个35:33的” mwad音响, AHA-8495819232。 8535模因536 [A71] 音响ANCE 92%9时33分:33, TM 853ech 可以577217 为”梅斯音响d, AHA-9141629909。 –
问题是您的代码正在解决该输入的问题,但不是针对上述输入。在同一行上获取相同的运行时错误。 –
时,有没有更多的[...]
对剩余你的问题发生,因为你没有退出循环。
如果更改
pos1 = InStr(pos1, Msg, "[")
立即有下面的代码后,我觉得你的问题就会迎刃而解:
If pos1 = 0 Then
Exit Do
End If
你的pos2
计算也应该从改变
pos2 = InStr(pos2, Msg, "]")
到
pos2 = InStr(pos1, Msg, "]")
这将确保你在当前[
后拿起第一]
的位置。
这两个改变后,你的代码正确处理两者现在在你的问题给出的例子...除了用于写出记录后在第二个例子[A17]
和[A21]
- 我不知道是否你真的希望他们被忽略,或者你的第二个例子是否在源数据的中途被截断。
上传你的代码的代码,所以我们可以用它(而不是作为图像) –
我猜想,'pos2'为零时坠毁 - 发表您的代码,我们可以帮助你弄清楚什么是错误的。 – YowE3K
也可以缩短调试时间,如果您让我们知道在出现此错误时您在“TextBox1”中输入了什么值 –