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
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