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