Hi,

I'm a hardware guy by trade so VB is very new to me (i.e. first time use
was yesterday). Anyway I have the following in a cell in Excel (Excel
2000 SP-3):

=INDEX(Sheet2!A1:A102,RANDBETWEEN(1,COUNTA(Sheet2! A1:A102)),1)

I have a list of words in the first column of sheet 2 and it grabs one
of the words at random. It works perfectly.

I have a 4x4 matrix that I'm randomly populating, so I have that
function in all 16 cells, but as you can imagine I sometimes get
duplicates. I don't want that so I cracked open VB (Visual Basic 6.0)
and I did the following:

'*****************CodeStart****************
Sub RandomWord()
'copy my list to the second column (this part is working)
Range("A1:A102").Copy Destination:=Range("B1:B102")

'cut one cell out randomly and place into C1 (Not Working)
Range(Application.WorksheetFunction.Index("B1:B102 ",
Application.WorksheetFunction.RANDBETWEEN(1,
Application.WorksheetFunction.CountA("B1:B102")), 2)).Cut
Destination:=Range("C1")

'call function to delete any empty cells in a row (this part is working)
Call del_empty_cell()

End Sub
'*****************CodeEnd****************

When I run the code I get the following error:
Runtime Error: 1004
"unable to get the Index property of WorksheetFunction class"

What confuses me is it seems that index (or perhaps CountA) is not
getting a valid value but I don't see how that can be so, because the
copy worked. Your help is appreciated.

-Jim Wick

Since you're working from outside the box (VB6 environment) named arguments
may not always work so drop the 'Destination:=' in two places. I couldn't
get it to work since I don't have your custom function CountA

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Jim Wickenhiser" wrote:
| Hi,
|
| I'm a hardware guy by trade so VB is very new to me (i.e. first time use
| was yesterday). Anyway I have the following in a cell in Excel (Excel
| 2000 SP-3):
|
| =INDEX(Sheet2!A1:A102,RANDBETWEEN(1,COUNTA(Sheet2! A1:A102)),1)
|
| I have a list of words in the first column of sheet 2 and it grabs one
| of the words at random. It works perfectly.
|
| I have a 4x4 matrix that I'm randomly populating, so I have that
| function in all 16 cells, but as you can imagine I sometimes get
| duplicates. I don't want that so I cracked open VB (Visual Basic 6.0)
| and I did the following:
|
| '*****************CodeStart****************
| Sub RandomWord()
| 'copy my list to the second column (this part is working)
| Range("A1:A102").Copy Destination:=Range("B1:B102")
|
| 'cut one cell out randomly and place into C1 (Not Working)
| Range(Application.WorksheetFunction.Index("B1:B102 ",
| Application.WorksheetFunction.RANDBETWEEN(1,
| Application.WorksheetFunction.CountA("B1:B102")), 2)).Cut
| Destination:=Range("C1")
|
| 'call function to delete any empty cells in a row (this part is working)
| Call del_empty_cell()
|
| End Sub
| '*****************CodeEnd****************
|
| When I run the code I get the following error:
| Runtime Error: 1004
| "unable to get the Index property of WorksheetFunction class"
|
| What confuses me is it seems that index (or perhaps CountA) is not
| getting a valid value but I don't see how that can be so, because the
| copy worked. Your help is appreciated.
|
| -Jim Wick

Not sure why you are doing this in VB6, but I think you want something like
this:

Sub test()

Dim LR As Long
Dim lRow As Long

LR = Cells(65536, 2).End(xlUp).Row
lRow = Int(LR * Rnd) + 1

Cells(3) = Cells(lRow, 2)
Cells(lRow, 2).Delete Shift:=xlUp

End Sub


RBS


"Jim Wickenhiser" wrote in message
. ..
Hi,

I'm a hardware guy by trade so VB is very new to me (i.e. first time use
was yesterday). Anyway I have the following in a cell in Excel (Excel
2000 SP-3):

=INDEX(Sheet2!A1:A102,RANDBETWEEN(1,COUNTA(Sheet2! A1:A102)),1)

I have a list of words in the first column of sheet 2 and it grabs one of
the words at random. It works perfectly.

I have a 4x4 matrix that I'm randomly populating, so I have that function
in all 16 cells, but as you can imagine I sometimes get duplicates. I
don't want that so I cracked open VB (Visual Basic 6.0) and I did the
following:

'*****************CodeStart****************
Sub RandomWord()
'copy my list to the second column (this part is working)
Range("A1:A102").Copy Destination:=Range("B1:B102")

'cut one cell out randomly and place into C1 (Not Working)
Range(Application.WorksheetFunction.Index("B1:B102 ",
Application.WorksheetFunction.RANDBETWEEN(1,
Application.WorksheetFunction.CountA("B1:B102")), 2)).Cut
Destination:=Range("C1")

'call function to delete any empty cells in a row (this part is working)
Call del_empty_cell()

End Sub
'*****************CodeEnd****************

When I run the code I get the following error:
Runtime Error: 1004
"unable to get the Index property of WorksheetFunction class"

What confuses me is it seems that index (or perhaps CountA) is not getting
a valid value but I don't see how that can be so, because the copy worked.
Your help is appreciated.

-Jim Wick

Jim,
I have modified my "BibleBingo" code to do what you want. (I think).
It chooses values at random from the selection and fills the range.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Sub BibleBingoModified()
'Jim Cone - December 17, 2004, modified 09/03/2006
'Fills a 4 x 4 matrix (Range("C1:F4")) with random values from the selection.

Dim varOldTest As Variant
Dim i As Long
Dim j As Long
Dim k As Long
Dim lngNum As Long
Dim arrNums() As Long
Dim arrBooks() As String

varOldTest = Excel.Selection.Value
lngNum = UBound(varOldTest, 1)
If lngNum < 16 Then
MsgBox "Not enough data. "
Exit Sub
End If

ReDim arrNums(0 To lngNum)
ReDim arrBooks(1 To 4, 1 To 4)
For i = 1 To 4
For j = 1 To 4
Do
Randomize (Right(Timer, 2) * i)
'Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
k = Int((lngNum * Rnd) + 1)
'prevents duplicates
If arrNums(k) < 999 Then
arrBooks(i, j) = varOldTest(k, 1)
arrNums(k) = 999
End If
Loop Until arrBooks(i, j) < vbNullString
Next 'j
Next 'i
'put names on worksheets
ActiveSheet.Range("C1:F4").Value = arrBooks()
End Sub
'-------------



"Jim Wickenhiser"

wrote in message
Hi,
I'm a hardware guy by trade so VB is very new to me (i.e. first time use
was yesterday). Anyway I have the following in a cell in Excel (Excel
2000 SP-3):

=INDEX(Sheet2!A1:A102,RANDBETWEEN(1,COUNTA(Sheet2! A1:A102)),1)

I have a list of words in the first column of sheet 2 and it grabs one
of the words at random. It works perfectly.

I have a 4x4 matrix that I'm randomly populating, so I have that
function in all 16 cells, but as you can imagine I sometimes get
duplicates. I don't want that so I cracked open VB (Visual Basic 6.0)
and I did the following:

-snip-

-Jim Wick

Dave Patrick wrote:
Since you're working from outside the box (VB6 environment) named arguments
may not always work so drop the 'Destination:=' in two places. I couldn't
get it to work since I don't have your custom function CountA


Thanks I'll give it try. (CountA is built-in function in Excel; it just
counts the number of cells in the range)

-Jim

VB6 is all I have (and it's just the version that comes with Office).
Thanks for the reply. I'll give this a try along with Jim Cone's.

-Jim Wick
RB Smissaert wrote:
Not sure why you are doing this in VB6, but I think you want something
like this:

Sub test()

Dim LR As Long
Dim lRow As Long

LR = Cells(65536, 2).End(xlUp).Row
lRow = Int(LR * Rnd) + 1

Cells(3) = Cells(lRow, 2)
Cells(lRow, 2).Delete Shift:=xlUp

End Sub


RBS


"Jim Wickenhiser" wrote in message
. ..
Hi,

I'm a hardware guy by trade so VB is very new to me (i.e. first time
use was yesterday). Anyway I have the following in a cell in Excel
(Excel 2000 SP-3):

=INDEX(Sheet2!A1:A102,RANDBETWEEN(1,COUNTA(Sheet2! A1:A102)),1)

I have a list of words in the first column of sheet 2 and it grabs one
of the words at random. It works perfectly.

I have a 4x4 matrix that I'm randomly populating, so I have that
function in all 16 cells, but as you can imagine I sometimes get
duplicates. I don't want that so I cracked open VB (Visual Basic 6.0)
and I did the following:

'*****************CodeStart****************
Sub RandomWord()
'copy my list to the second column (this part is working)
Range("A1:A102").Copy Destination:=Range("B1:B102")

'cut one cell out randomly and place into C1 (Not Working)
Range(Application.WorksheetFunction.Index("B1:B102 ",
Application.WorksheetFunction.RANDBETWEEN(1,
Application.WorksheetFunction.CountA("B1:B102")), 2)).Cut
Destination:=Range("C1")

'call function to delete any empty cells in a row (this part is working)
Call del_empty_cell()

End Sub
'*****************CodeEnd****************

When I run the code I get the following error:
Runtime Error: 1004
"unable to get the Index property of WorksheetFunction class"

What confuses me is it seems that index (or perhaps CountA) is not
getting a valid value but I don't see how that can be so, because the
copy worked. Your help is appreciated.

-Jim Wick

Hi Jim,

It's like you read my mind. :) I'm making a bingo game for daughter to
practice the "102 words students should know".

Thanks to everyone for your input I'll report back on how things go.

-Jim Wick

Jim Cone wrote:
Jim,
I have modified my "BibleBingo" code to do what you want. (I think).
It chooses values at random from the selection and fills the range.

Jim,
Now if I just knew who was going to win the super bowl. <g

The Bible Bingo workbook (and the Bingo Cards) workbook(s)
are available for the asking. Remove xxx from my email address.
Sincerely,
Jim Cone
XX

"Jim Wickenhiser"

wrote in message
Hi Jim,
It's like you read my mind. :) I'm making a bingo game for daughter to
practice the "102 words students should know".
Thanks to everyone for your input I'll report back on how things go.

-Jim Wick

Jim Cone wrote:
Jim,
I have modified my "BibleBingo" code to do what you want. (I think).
It chooses values at random from the selection and fills the range.

Hi Jim,

Worked perfectly. I stepped through your code and what you're doing is
much more elegant than my clumsy attempt. I did modify one thing though
(I can't help myself); since I know the maximum number of words, I put
an upper bound on lngNum. That way my wife can select the whole column
(or a subset) without any negative consequence.

Here's my addition (in my code upperBound is actually declared as a
Const but I put it here for clarity):


varWordBingo = Excel.Selection.Value
UPPER_BOUND = 102
lngNumTemp = UBound(varWordBingo, 1)
If upperBound < lngNumTemp Then
lngNum = UPPER_BOUND
Else
lngNum = lngNumTemp
End If


Thus ends my temporary foray into VB; thank you all very much for your
help and if you need some Verilog help let me know. :)

-Jim Wick

Jim Cone wrote:
Jim,
I have modified my "BibleBingo" code to do what you want. (I think).
It chooses values at random from the selection and fills the range.

Maybe by now you have realised that you are working in VBA 6 inside Excel
and not VB 6 (outside Excel) which caused two people to provided "weird"
answers.

--
Regards,
Tom Ogilvy

"Jim Wickenhiser" wrote in message
. ..
Hi Jim,

Worked perfectly. I stepped through your code and what you're doing is
much more elegant than my clumsy attempt. I did modify one thing though
(I can't help myself); since I know the maximum number of words, I put an
upper bound on lngNum. That way my wife can select the whole column (or a
subset) without any negative consequence.

Here's my addition (in my code upperBound is actually declared as a Const
but I put it here for clarity):


varWordBingo = Excel.Selection.Value
UPPER_BOUND = 102
lngNumTemp = UBound(varWordBingo, 1)
If upperBound < lngNumTemp Then
lngNum = UPPER_BOUND
Else
lngNum = lngNumTemp
End If


Thus ends my temporary foray into VB; thank you all very much for your
help and if you need some Verilog help let me know. :)

-Jim Wick

Jim Cone wrote:
Jim,
I have modified my "BibleBingo" code to do what you want. (I think).
It chooses values at random from the selection and fills the range.