Error 0: subscript out of order when populating an array

[AnswerSeeker]

I am trying to extract names from activesheet in a certain range into an undimensioned array and also show the results on another worksheets, but I always get the error message "subscript out of range".

The code:


Dim arrUsedNames()

numbers = ActiveSheet.UsedRange.Rows.Count
For line = 4 To numbers Step 1
arrUsedNames(Max) = ActiveSheet.Cells(line, 4).Value
Worksheets(2).Range("A1").Offset(Max, 1).Value = arrUsedNames(Max)
Next line

[Bob Phillips]

I assume there is more to it than what you show, otherwise why not just copy one range to another, but this works


Dim arrUsedNames As Variant
Dim numbers As Long
Dim Max As Long
Dim Line As Long

numbers = ActiveSheet.UsedRange.Rows.Count
ReDim arrUsedNames(1 To numbers - 3)
Max = 0
For Line = 4 To numbers Step 1
Max = Max + 1
arrUsedNames(Max) = ActiveSheet.Cells(Line, 4).Value
Next Line

ReDim Preserve arrUsedNames(1 To Max)
Worksheets(2).Range("A1").Resize(Max, 1).Value = Application.Transpose(arrUsedNames)

[AnswerSeeker]

yes, that works. Thx.

And yes, it is just a part and I just hope that will be fine for my own purposes.

[AnswerSeeker]

Okay, I have a better Idea what I want. I have a whole workbook, where all sheets contain a list with names. Many names appear many times, some just rarely (i.e. some sheets list more names than others). I want to create a loop that go through all worksheet (except the first 2) and assign the names to an array, of course I don't want any duplications. Later the array will be used to fill up an listbox (which itself wil be used to determine one name as search criteria).
My code so far:

Sub CollectNames()
Dim myArray() As Variant

numbers = 1
ReDim myArray(1 To numbers)

For wkSht = 3 To ActiveWorkbook.Worksheets.Count
Worksheets(wkSht).Activate
CONTINUE:
AlreadyThere = "no"
Crow= ActiveSheet.UsedRange.Rows.Count
For Cline= 4 To Crow - 3 'names are in column D and start at line 4
For check = 1 To numbers
If myArray(check) = Cells(Cline, 4).Value Then
AlreadyThere = "yes"
check = numbers
End If
Next check
If AlreadyThere = "no" Then
myArray(numbers) = Cells(Cline, 4).Value
numbers = numbers + 1
ReDim Preserve myArray(1 To numbers)
Else
GoTo CONTINUE
End If
Next line
Next wkSht

End Sub


My error msg is always 'subscript out of range'

[AnswerSeeker]

ah, I found my mistake, I typed " myArrar" rather than "myArray" :banghead:

But now, the code is running unlimited. The first worksheets is doing fine, but then in the second worksheet is getting into a endless loop.

[Bob Phillips]

Can you help us by posting the workbook?

[snb]

sub snb()
' set reference to scripting runtime
with new dictionary
for each sh in sheets
if Instr("aabb",sh.name)=0 then
for each cl in sh.columns(4).specialcells(2)
if cl.row>4 and not .exists(cl.value) then .add cl.value,""
next
end if
next

combobox1.list=.keys
end with
end sub