-
Solved: Error '1004' pops up randomly
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!
[vba]
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
[/vba]
-
Hi Icestone,
Welcome to VBAX. Can you post a workbook with some data to test. Use Manage Attachments in the Go Advanced reply section
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
I'd lay odds that
[vba]Int(usedrow * Rnd(4))[/vba]is occasionally producing 0, then the next line[vba]selword = Cells(prow, 1).Value[/vba]effectively becomes
[vba]selword = Cells(0, 1).Value[/vba]and 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)
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
-
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!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules