Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

subscript out of range

  Asked By: Sam    Date: Aug 11    Category: MS Office    Views: 1327
  

I am a relatuivly new to vb and vba. I used Mark Thorpe's VBA
lessons, and tried to modify one of the homework assignemnts.
Instead of deleteing and counting dupicate rows, I need to put a
list of non repeating words into an array for later use. Here is
what I have:

Sub RemoveDuplicates()
Dim SrvNameArry() As String 'used to store array of server name
for ne workbook
Dim count, totalrows, row As Integer ' used as a counter


totalrows = ActiveSheet.UsedRange.Rows.count
count = 0
For row = totalrows To 2 Step -1
If Cells(row, 1).Value = Cells(row - 1, 1).Value Then
SrvNameArry(count) = Cells(row, 1).Value
count = count + 1
End If
Next row

End Sub

When I step through it, I get an out of sub script range when it
tries to assign the word to the array. I do not have Option Base
set so I was under the impression all arrays began at 0.

Any ideas?

Share: 

 

4 Answers Found

 
Answer #1    Answered By: Skye Hughes     Answered On: Aug 11

Does it make any difference if you declare the array  as a varient,
rather than as a string?

 
Answer #2    Answered By: Funsani Chalthoum     Answered On: Aug 11

No, I found it does work if I decalre the Cariable as a specific
size ie MyAray(300). This really doesn't hlp me though

 
Answer #3    Answered By: Randall Franklin     Answered On: Aug 11

this works on Excel XP.

I'm finding XL-VBA a bit tougher than Access VBA... but a new
challenge is fun.

Note that to declare an Array with a variable as the size you need to
use 'ReDim', instead of 'Dim' (new to me as well).

 
Answer #4    Answered By: Josie Roberts     Answered On: Aug 11

accidently declared the array  twice - you can drop the first
declaration.

--------------------------------
Sub RemoveDuplicates()

Dim count, totalrows, row  As Integer ' used as a counter
totalrows = ActiveSheet.UsedRange.Rows.count
ReDim SrvNameArry(totalrows) As String
count = 0

For row = totalrows To 2 Step -1
If Cells(row, 1).Value = Cells(row - 1, 1).Value Then
SrvNameArry(count) = Cells(row, 1).Value
count = count  + 1
End If
Next row

End Sub
------------------------------------

 
Didn't find what you were looking for? Find more on subscript out of range Or get search suggestion and latest updates.




Tagged: