I have 2 sheets. I am using a user-defined function in sheet 1, in which I want to use an array to compare some strings. The array is comprised of the contents of a column of cells in the second sheet (which is named "phrases.").
我有两个表。我在表1中使用了一个用户定义的函数，我想在其中使用一个数组来比较一些字符串。数组由第二张表(命名为“短句”)中的单元格列的内容组成。
So (looking at it another way) in "phrases" I have 100 strings typed into column P, cells 3 to 102. And I want to put all of them into an array that i can use later.
所以(换个角度看)在"短句"中我有100个字符串输入到P列，单元格3到102。我想把它们都放到一个数组中，以便以后使用。
Now, let me complicate it a little - my intent is that users of the spreadsheet will be able to add new content to column P, so that it may eventually be 500 cells or more. So I really want to populate that array dynamically.
现在，让我把它复杂化一点——我的目的是电子表格的用户将能够向P列添加新的内容，这样它最终可能是500个单元格或更多。所以我想动态地填充这个数组。
Here's where i am - and it doesn't seem to be working:
这就是我所在的地方——它似乎并不奏效:
Dim newarray() As String
Dim i As Long
Dim counter As Long
counter = 0
For i = 0 To 5000
If Worksheets("phrases").Cells(i + 3, 16).Value <> 0 Then
newarray(counter) = Worksheets("phrases").Range(i + 3, 16).Value
counter = counter + 1
End If
Next
Where am i going wrong?
我哪里出错了?
Please note - I've tried this without .Value - didn't seem to work. I've tried this with .Text instead of .Value - didn't seem to work. I've tried CStr(Worksheets("phrases").Range(i + 3, 16).Value) and several variations - and it didn't seem to work.
请注意——我试过这个没有。value——似乎没有效果。我用。text而不是。value尝试过这个方法，但似乎没有效果。我试着装运箱(工作表(“短语”)。范围(i + 3, 16). value)和一些变化-它似乎不起作用。
I expect there is something simple I am missing here - but i have no idea what.
我希望这里有一些简单的东西我错过了——但我不知道什么。
3 个解决方案
#1
1
Dim newarray() As String
Dim i As Long
Dim lr AS Long
Dim counter As Long
lr = ActiveSheet.Range("P" & Rows.Count).End(xlUp).Row
counter = 0
For i = 1 To lr
If Worksheets("phrases").Range("P" & i).value <> 0 Then
Redim Preserve newarray(counter)
newarray(counter) = Worksheets("phrases").Range("P" & i).value
counter = counter + 1
End If
Next
#2
0
First construct a comma-separated string. Then convert the string into an array using the Split() function.
首先构造一个逗号分隔的字符串。然后使用Split()函数将字符串转换为数组。
#3
0
You can make the array directly from the cells without having to loop at all using a single line of code, this will also capture anything that the user adds to the bottom of column P by using the Resize() method:
您可以直接从单元格中创建数组，而不必使用一行代码进行循环，这也将捕获用户通过Resize()方法添加到P列底部的任何内容:
Sub SO()
stringArray = [P3:P102].Resize(Range("P" & Rows.Count).End(xlUp).Row - 2, 1)
'// Print each value in the array
For Each x In stringArray
Debug.Print CStr(x)
Next x
End Sub
#1
1
Dim newarray() As String
Dim i As Long
Dim lr AS Long
Dim counter As Long
lr = ActiveSheet.Range("P" & Rows.Count).End(xlUp).Row
counter = 0
For i = 1 To lr
If Worksheets("phrases").Range("P" & i).value <> 0 Then
Redim Preserve newarray(counter)
newarray(counter) = Worksheets("phrases").Range("P" & i).value
counter = counter + 1
End If
Next
#2
0
First construct a comma-separated string. Then convert the string into an array using the Split() function.
首先构造一个逗号分隔的字符串。然后使用Split()函数将字符串转换为数组。
#3
0
You can make the array directly from the cells without having to loop at all using a single line of code, this will also capture anything that the user adds to the bottom of column P by using the Resize() method:
您可以直接从单元格中创建数组，而不必使用一行代码进行循环，这也将捕获用户通过Resize()方法添加到P列底部的任何内容:
Sub SO()
stringArray = [P3:P102].Resize(Range("P" & Rows.Count).End(xlUp).Row - 2, 1)
'// Print each value in the array
For Each x In stringArray
Debug.Print CStr(x)
Next x
End Sub