PDA

View Full Version : Solved: Error '1004' pops up randomly



Icestone
03-08-2009, 08:16 AM
Hi guys, I've been stuck with this problem for quite some time now. Hope somebody out there can let me know what's wrong with my source code.

This macro is supposed to aid me with my language learning. I'll key in the words that i've learnt in column 1. When run, the macro will randomly select words from that list to be displayed on a msgbox. I don't want selected words to be repeated, so all the previously selected words will be listed on column 2. The 'Find' method will be invoked within the macro to check if the newly selected word is already in the list in column 2. If it is, then the word will not be listed again in column 2. I've also created 2 dynamic ranges in the worksheet; in column 1 and column 2 to facilitate this purpose.

There's a bug with the source code though. Error '1004'. Sometimes it appears when the list is still very short (about 5-6 entries) and sometimes the list can be populated up to 40+ entries before the error occurs. One thing i notice though, the error always occur in the following line in the code:
selword = Cells(prow, 1).Value

Can somebody pls tell me what's wrong?? Thanks in advance!


Option Explicit

Public Sub Word()

Dim usedrow As Integer
Dim prow As Integer
Dim digit As Integer
Dim cown As Integer
Dim Rng As Range
Dim selword As String
Dim wordlist As Range


digit = Cells(2, 3).Value
usedrow = ActiveSheet.UsedRange.Rows.count

If digit > 0 Then

For cown = 1 To digit
prow = Int(usedrow * Rnd(4))
selword = Cells(prow, 1).Value

With Sheets("Sheet1").Range("wordlist")
Set Rng = .Find(What:=selword, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False)
If Not Rng Is Nothing Then
cown = cown - 1
Else
Cells(cown + 1, 2).Value = selword
MsgBox selword
End If
End With

Next cown

Else
MsgBox "Please enter the number of words."

End If

End Sub

mdmackillop
03-08-2009, 08:29 AM
Hi Icestone,
Welcome to VBAX. Can you post a workbook with some data to test. Use Manage Attachments in the Go Advanced reply section

p45cal
03-08-2009, 08:46 AM
I'd lay odds that
Int(usedrow * Rnd(4))is occasionally producing 0, then the next lineselword = Cells(prow, 1).Valueeffectively becomes
selword = Cells(0, 1).Valueand there is no row 0. Look at the help file on Rnd which suggests:
To produce random integers in a given range, use this formula:
Int((upperbound - lowerbound + 1) * Rnd + lowerbound)

Icestone
03-08-2009, 07:35 PM
Hi! Thank you so much for the quick replies!
I've attached a copy of the spreadsheet together with this post.
I've also done as p45cal suggested and yes, it got rid of the bug!

Thanks a lot again!