dividere la string con la scheda verticale come delimitatore usando vba

Come posso dividere una string in vba con la scheda verticale come delimitatore.

strWriters = Mid("Richenn M.(there a delimiter here)QA'er:", 9, Len("Richenn M.(there a delimiter here)QA'er:")) LArray = VBA.Split(Selection.Text, Chr(103)) strWriter = LArray(0) MsgBox LArray(0) 

ho cercato di cercare l'equivalente char, ma restituisce un errore per char (103)

Dice il tipo di disallineamento.

Il carattere della linguetta verticale è ASCII & H0B hex o 011 dec¹. VBA utilizza le funzioni Chr e Asc mentre il foglio di lavoro utilizza le funzioni CHAR e CODE .

La tua syntax sembra in gran parte corretto, ma c'è qualche confusione riguardo a cosa stai lavorando. Si inizia con una costante di string e una variabile di string (ad esempio strWriters ), quindi scartarli immediatamente e lavorare con Selection.Text .

 Sub how2split() Dim str As String, strWriters As String, a As Long, LArray As Variant str = "Richenn M." & Chr(&HB) & "QA'er:" 'alternate 'str = Join(Array("Richenn M.", "QA'er:"), Chr(&HB)) Debug.Print str LArray = Split(str, Chr(11)) For a = LBound(LArray) To UBound(LArray) Debug.Print LArray(a) & " is at position " & a Next a End Sub Sub how2split () Sub how2split() Dim str As String, strWriters As String, a As Long, LArray As Variant str = "Richenn M." & Chr(&HB) & "QA'er:" 'alternate 'str = Join(Array("Richenn M.", "QA'er:"), Chr(&HB)) Debug.Print str LArray = Split(str, Chr(11)) For a = LBound(LArray) To UBound(LArray) Debug.Print LArray(a) & " is at position " & a Next a End Sub Dim str come string, strWriters come string, una lunghezza, LArray come variante Sub how2split() Dim str As String, strWriters As String, a As Long, LArray As Variant str = "Richenn M." & Chr(&HB) & "QA'er:" 'alternate 'str = Join(Array("Richenn M.", "QA'er:"), Chr(&HB)) Debug.Print str LArray = Split(str, Chr(11)) For a = LBound(LArray) To UBound(LArray) Debug.Print LArray(a) & " is at position " & a Next a End Sub str = "Richenn M." Sub how2split() Dim str As String, strWriters As String, a As Long, LArray As Variant str = "Richenn M." & Chr(&HB) & "QA'er:" 'alternate 'str = Join(Array("Richenn M.", "QA'er:"), Chr(&HB)) Debug.Print str LArray = Split(str, Chr(11)) For a = LBound(LArray) To UBound(LArray) Debug.Print LArray(a) & " is at position " & a Next a End Sub & Chr (& HB) & "QA'er:" Sub how2split() Dim str As String, strWriters As String, a As Long, LArray As Variant str = "Richenn M." & Chr(&HB) & "QA'er:" 'alternate 'str = Join(Array("Richenn M.", "QA'er:"), Chr(&HB)) Debug.Print str LArray = Split(str, Chr(11)) For a = LBound(LArray) To UBound(LArray) Debug.Print LArray(a) & " is at position " & a Next a End Sub 'str = Join (Array ("Richenn M.", "QA'er:"), Chr (& HB)) Sub how2split() Dim str As String, strWriters As String, a As Long, LArray As Variant str = "Richenn M." & Chr(&HB) & "QA'er:" 'alternate 'str = Join(Array("Richenn M.", "QA'er:"), Chr(&HB)) Debug.Print str LArray = Split(str, Chr(11)) For a = LBound(LArray) To UBound(LArray) Debug.Print LArray(a) & " is at position " & a Next a End Sub Debug.Print str Sub how2split() Dim str As String, strWriters As String, a As Long, LArray As Variant str = "Richenn M." & Chr(&HB) & "QA'er:" 'alternate 'str = Join(Array("Richenn M.", "QA'er:"), Chr(&HB)) Debug.Print str LArray = Split(str, Chr(11)) For a = LBound(LArray) To UBound(LArray) Debug.Print LArray(a) & " is at position " & a Next a End Sub LArray = Split (str, Chr (11)) Sub how2split() Dim str As String, strWriters As String, a As Long, LArray As Variant str = "Richenn M." & Chr(&HB) & "QA'er:" 'alternate 'str = Join(Array("Richenn M.", "QA'er:"), Chr(&HB)) Debug.Print str LArray = Split(str, Chr(11)) For a = LBound(LArray) To UBound(LArray) Debug.Print LArray(a) & " is at position " & a Next a End Sub Per un = LBound (LArray) A UBound (LArray) Sub how2split() Dim str As String, strWriters As String, a As Long, LArray As Variant str = "Richenn M." & Chr(&HB) & "QA'er:" 'alternate 'str = Join(Array("Richenn M.", "QA'er:"), Chr(&HB)) Debug.Print str LArray = Split(str, Chr(11)) For a = LBound(LArray) To UBound(LArray) Debug.Print LArray(a) & " is at position " & a Next a End Sub Debug.Print LArray (a) & "è in posizione" & a Sub how2split() Dim str As String, strWriters As String, a As Long, LArray As Variant str = "Richenn M." & Chr(&HB) & "QA'er:" 'alternate 'str = Join(Array("Richenn M.", "QA'er:"), Chr(&HB)) Debug.Print str LArray = Split(str, Chr(11)) For a = LBound(LArray) To UBound(LArray) Debug.Print LArray(a) & " is at position " & a Next a End Sub Avanti a Sub how2split() Dim str As String, strWriters As String, a As Long, LArray As Variant str = "Richenn M." & Chr(&HB) & "QA'er:" 'alternate 'str = Join(Array("Richenn M.", "QA'er:"), Chr(&HB)) Debug.Print str LArray = Split(str, Chr(11)) For a = LBound(LArray) To UBound(LArray) Debug.Print LArray(a) & " is at position " & a Next a End Sub 

Il command Debug.Print invia l'output alla window Immediata di VBE.


¹ ASCII 103 è un G minuscolo (ad esempio Chr(103) produce un g ).