Excel 2010 VBA通过一个字符串,并将一个字符顺序放入每个单元格 [英] Excel 2010 VBA step through a string and place one char into each cell in sequence
问题描述
现在我已经有了这个具体的任务。创建一个字符串这是一个字符串并创建了一个新的工作簿。
现在我需要使用字符串切片来将't' A2中的'h',A3中的'我'等到字符串的末尾。
之后我的下一个字符串将进入,说B1等,直到所有字符串被切片。
我已经搜索,但是似乎大多数人都想要这样做(连接一个范围)。
任何想法?
使用中间函数。
= MID($ A $ 1,1,1)
第二个参数是起始位置,因此您可以将其替换为行或列函数,以便您可以动态拖动公式。
ie。
= MID($ A $ 1,ROW(),1)
如果你想纯粹在VBA中做,我相信中间功能也存在于那里,所以只需循环字符串。
Dim str as String
str = Sheet1.Cells(1,1).Value
for i = 1 to Len(str)
'输出字符串列中的一个字符C
sheet1.cells(i,3).value = Mid(str,i,1 )
next i
*编辑*
如果要使用数组中的多个字符串执行此操作,可以使用以下内容:
Dim str(1 to 2)as String
str(1)=这是一个测试字符串
str(2)=一些更多的测试文本
为j = Lbo (str)到Ubound(str)
for i = 1 to Len(str(j))
'输出字符串A和B中的一个字符一个字符
sheet1.cells i,j).value = Mid(str(j),i,1)
next i
next j
I am used to string slicing in 'C' many, many years ago but I am trying to work with VBA for this specific task.
Right now I have created a string "this is a string" and created a new workbook.
What I need now is to use string slicing to put 't' in, say, A1, 'h' in A2, 'i' in A3 etc. to the end of the string.
After which my next string will go in, say B1 etc. until all strings are sliced.
I have searched but it seems most people want to do it the other way around (concatenating a range).
Any thoughts?
Use the mid function.
=MID($A$1,1,1)
The second argument is the start position so you could replace that for something like the row or col function so you can drag the formula dynamically.
ie.
=MID($A$1,ROW(),1)
If you wanted to do it purely in VBA, I believe the mid function exists in there too, so just loop through the string.
Dim str as String
str = Sheet1.Cells(1,1).Value
for i = 1 to Len(str)
'output string 1 character at a time in column C
sheet1.cells(i,3).value = Mid(str,i,1)
next i
* edit *
If you want to do this with multiple strings from an array, you could use something like:
Dim str(1 to 2) as String
str(1) = "This is a test string"
str(2) = "Some more test text"
for j = Lbound(str) to Ubound(str)
for i = 1 to Len(str(j))
'output strings 1 character at a time in columns A and B
sheet1.cells(i,j).value = Mid(str(j),i,1)
next i
next j
这篇关于Excel 2010 VBA通过一个字符串,并将一个字符顺序放入每个单元格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!