在Excel中读取单元格内容,基于定界符进行解析并插入数组 [英] read cell content in Excel, parse based on delimiter and insert into array
问题描述
我是Excel VBA的新手,所以请多多包涵。
I am a total novice with Excel VBA so please bear with me.
我试图基于定界符:解析单元格值。定界符之前和之后的值将存储在数组中,以便我可以在WHERE中引用数组值COL1 = [数组中:之前的值]和COL2 = [数组中:之后的对应值]在SQL查询中。
I am trying to parse the cell values based on the delimiter ":". The value before the delimiter and after the delimiter will be stored in an array so that I can reference the array values in WHERE COL1 = [value before ":" in array] AND COL2= [corresponding value after ":" in array] clause in a SQL query.
我的数据如下:
T:NYSE
ABX:NASDAQ
V:AIM
(blank)
009123:OTDCXE
(blank)
xxx:dhgjdg
等。数据存储在固定范围A1:A10中,但可能不是连续的。我想使用记录集运行SQL语句。例如
and so on. The data is stored in at a fixed range A1:A10 but may not be contiguous. I would want to run the SQL statement using a recordset .e.g
for i=0 to [last element in array]
'run SELECT statement for each element (excluding nulls)
next
以下是什么到目前为止,我已经做过了,但是没有用:
Following is what I have done so far and it's not working:
Sub test3()
Dim MyArray()
'Dim MyArray()
Dim MyRange As Range
Dim RowCount As Long
Dim ColCount As Long
Dim R As Long
Dim C As Long
Set MyRange = Range("A1:a10") ' <-- Adjust!!!
RowCount = 10 ' <-- Adjust as necessary
ColCount = 1 ' <-- Adjust as necessary
ReDim MyArray(1 To RowCount, 1 To ColCount)
For R = 1 To RowCount
For C = 1 To ColCount
MyArray(R, C) = MyRange(R, C).Value
Next C
Next R
'Following was done only for debugging purpose
For i = 1 To UBound(MyArray)
For j = 1 To UBound(MyArray)
Sheets(1).Cells(i + 20, j).Value = MyArray(i, j).Value
Next j
Next i
End Sub
如果有人可以指出我在做什么错,并提供一些指导。我花了三天的时间来找出解决方案,以便我可以提取所有非空值并在sql语句中使用它们。
If anyone can point out what am I doing incorrect and provide some guidance. I have spent my past three days to figure out a solution so that i can extract all non-null values and use them in the sql statement.
在此先感谢您
编辑1:
最后,做了一些挖掘并提出了一些建议,其中98%可行。
Finally, did some digging around and came up with something, 98% of which works.
示例输入:
LEG:NYSE
LEG:TAM
SPCC:AIM
ONTPC:ZWSESA
0943292:owndgt
b:a
d:ee
f:aaaaaa
最终输出:
LEG NYSE
LEG :TAM
SPCC C:AIM
ONTPC ZWSESA
0943292 2:owndgt
b :a
d ee
f aa
不知道为什么我没有在:之后得到完整的字符串。
以下是上述输出的完整代码
Not sure why I am not getting the complete string after ":". Below is my complete code for the above output
Sub test2()
Set ws = ThisWorkbook.Sheets("Sheet1")
MyArray = ws.Range("A1:A10")
Range("A11:A30").ClearContents
ReDim newarr(LBound(MyArray) To UBound(MyArray))
For i = LBound(MyArray) To UBound(MyArray)
If MyArray(i, 1) <> "" Then
j = j + 1
newarr(j) = MyArray(i, 1)
End If
Next i
ReDim Preserve newarr(LBound(MyArray) To j)
For R = 1 To UBound(newarr) ' First array dimension is rows.
ws.Cells(R + 12, 1).Value = newarr(R)
Next R
'Store values preceding and succeeding the delimiter in two arrays
ReDim lftarray(LBound(newarr) To UBound(newarr))
ReDim rtarray(LBound(newarr) To UBound(newarr))
For i = LBound(newarr) To UBound(newarr)
lftarray(i) = Split(newarr(i), ":")
rtarray(i) = Right(newarr(i), InStr(1, newarr(i), ":"))
Next i
ReDim Preserve lftarray(LBound(newarr) To i)
ReDim Preserve rtarray(LBound(newarr) To i)
'Print values before ":"
For i = LBound(lftarray) To UBound(lftarray) ' First array dimension is rows.
ws.Cells(i + 24, 1).Value = lftarray(i)
Next i
'Print values after ":"
For i = LBound(rtarray) To UBound(rtarray)
ws.Cells(i + 24, 2).Value = rtarray(i)
Next i
End Sub
Edit2:
Sub test2()
Set ws = ThisWorkbook.Sheets("Sheet1")
MyArray = ws.Range("A1:A10")
Range("A11:A30").ClearContents
ReDim newarr(LBound(MyArray) To UBound(MyArray))
For i = LBound(MyArray) To UBound(MyArray)
If MyArray(i, 1) <> "" Then
j = j + 1
newarr(j) = MyArray(i, 1)
End If
Next i
ReDim Preserve newarr(LBound(MyArray) To j)
For R = 1 To UBound(newarr) ' First array dimension is rows.
ws.Cells(R + 12, 1).Value = newarr(R)
Next R
'Store values preceding and succeeding the delimiter in two arrays
ReDim lftarray(LBound(newarr) To UBound(newarr))
ReDim rtarray(LBound(newarr) To UBound(newarr))
For i = LBound(newarr) To UBound(newarr)
lftarray(i) = Split(newarr(i), ":")
rtarray(i) = Right(newarr(i), Len(newarr(i)) - InStr(1, newarr(i), ":"))
Next i
ReDim Preserve lftarray(LBound(newarr) To i)
ReDim Preserve rtarray(LBound(newarr) To i)
'Print values before ":"
For i = LBound(lftarray) To UBound(lftarray) ' First array dimension is rows.
ws.Cells(i + 24, 1).Value = lftarray(i)
Next i
'Print values after ":"
For i = LBound(rtarray) To UBound(rtarray)
ws.Cells(i + 24, 2).Value = rtarray(i)
Next i
End Sub
推荐答案
这是我的答案
Sub test2()
Set ws = ThisWorkbook.Sheets("Sheet1")
MyArray = ws.Range("A1:A10")
Range("A11:A30").ClearContents
ReDim newarr(LBound(MyArray) To UBound(MyArray))
For i = LBound(MyArray) To UBound(MyArray)
If MyArray(i, 1) <> "" Then
j = j + 1
newarr(j) = MyArray(i, 1)
End If
Next i
ReDim Preserve newarr(LBound(MyArray) To j)
For R = 1 To UBound(newarr) ' First array dimension is rows.
ws.Cells(R + 12, 1).Value = newarr(R)
Next R
'Store values preceding and succeeding the delimiter in two arrays
ReDim lftarray(LBound(newarr) To UBound(newarr))
ReDim rtarray(LBound(newarr) To UBound(newarr))
For i = LBound(newarr) To UBound(newarr)
lftarray(i) = Split(newarr(i), ":")
rtarray(i) = Right(newarr(i), Len(newarr(i)) - InStr(1, newarr(i), ":"))
Next i
ReDim Preserve lftarray(LBound(newarr) To i)
ReDim Preserve rtarray(LBound(newarr) To i)
'Print values before ":"
For i = LBound(lftarray) To UBound(lftarray) ' First array dimension is rows.
ws.Cells(i + 24, 1).Value = lftarray(i)
Next i
'Print values after ":"
For i = LBound(rtarray) To UBound(rtarray)
ws.Cells(i + 24, 2).Value = rtarray(i)
Next i
End Sub
这篇关于在Excel中读取单元格内容,基于定界符进行解析并插入数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!