Increase your Karma Points -Help a newbie out

[Jim Wickenhiser]

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

 

[Dave Patrick]

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

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

 

[RB Smissaert]

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

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"
<[email protected]>
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

 

[Jim Wickenhiser]

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

 

[Jim Wickenhiser]

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

 

[Jim Wickenhiser]

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]

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
(e-mail address removed)

"Jim Wickenhiser"
<[email protected]>
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 Wickenhiser]

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

 

[Tom Ogilvy]

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.