Hello,
Newbie to VBScript and having an issue. I was lucky enough to get a
responce to an earlier post about setting up a macro but when testing it I
get an error:

Run-time error '1004':
Method 'Range' of object '_worksheet' failed.

This is exactly what I have.
I have a workbook called "macro test.xls" inside are two worksheets called
sheet1 and sheet2
Sheet1 has all the data, sheet 2 is blank

Sheet 1 uses Column A through F
Col. A = 1 through 1000 (1000 rows)
Col. B through F has random numbers (like a lottery)

So it looks exactly like this:
A B C D E F
1 02 25 35 41 42
2 06 09 22 42 44
3 01 08 10 28 35
4 14 23 31 32 41
5 27 29 30 36 43

As from the earlier post I am looking to get Sheet2 to auto populate the
numbers 1 through 99 in Sheet2 Col. A then have whatever row that number is
found on Sheet1 to populate on Sheet2 Col. B through whatever.

The code I was given is:


Sub FindRows()

Dim rng As Range, rngA As Range, c As Range
Dim ws1 As Worksheet, ws2 As Worksheet
Dim i As Integer, idx As Long

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

ws1.Select
' Change this range as required
Set rng = ws1.Range("A1:F1000" & Cells(Rows.Count, "A").End(xlUp).Row)

For i = 1 To 99 ' Set numbers 1 to 99 in col A of Sheet2
ws2.Cells(i, 1) = i
Next i

For Each c In rng ' Loop through each cell in selected range
idx = c.Value ' Row index in Sheet 2
With ws2
Set rngA = .Range("A" & Trim(Str(idx)) & ":IV" & Trim(Str(idx)))
' COUNTA is used to determine last used column for selected row IDX
.Cells(idx, Application.CountA(rngA) + 1) = c.Row ' Add to next column
End With
Next c

When I execute I get the 1004 error message and when I click on Debug I get
a yellow highlight on:

Set rng = ws1.Range("A1:F1000" & Cells(Rows.Count, "A").End(xlUp).Row)

Can someone help me find why I am getting the error and how I may correct
it. I did google searches and 1004 has a few reasons like a different
workbook or overwriting which did not seem the issue.

Thank you and sorry for the length of e-mail, I wanted to be thorough.
David