Run-time error '1004'

[Guest]

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

 

[Nick Hebb]

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

It's unclear what you're trying to do here. The <<Cells(Rows.Count,
"A").End(xlUp).Row>> portion will return 1000 if your used range is
A1:F1000. So you are concatenating 1000 onto the end of F1000, for a
range of A1:F10001000 - probably not what you intended.

Tell us what range are you trying to select and then it will be easier
to supply a fix.

Also, from your description, it sounds like you are trying to transpose
the data from sheet1 onto sheet2. If so, you will run out of columns
since you have 1000 rows selected and there are only 256 columns in
Excel.

 

[Chip Pearson]

David,

Are you sure you want the '1000' in the line of code:

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

If there is data in row 10 or greater, this becomes, for example

ws1.Range("A1:F100010")

which contains more rows that are allowed in a worksheet. I
suspect you want the code to read as follows:

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


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Nigel]

If the range on sheet1 is fixed as A1:F1000 then try using the following in
place of the code throwing the error.

Set rng = ws1.Range("A1:F1000")

 

[JE McGimpsey]

First, Cells defaults to the ActiveSheet, so

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

is equivalent to

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

Second, even if ws1 were the active sheet, and if the last used cell in
column A were, say A100,

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

would try to set the range to

ws1.Range("A1:F1000100")

which would give you an out of range.

I suspect you're trying for something like:

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

or perhaps

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

 

[Guest]

Here is the code for doing what you want.

Sub Test()
Dim i%, j%
With Sheet1
For i = 1 To 99
.Range("A1:F1000").Sort Key1:=.Columns(1), Order1:=xlAscending
.Range("G1").FormulaArray = "=Sum(--($B1:$F1=" & i & "))"
.Range("G1").Copy .Range("G2:G1000")
.Range("A1:G1000").Sort Key1:=.Columns(7), Order1:=xlDescending
j = WorksheetFunction.CountIf(.Range("G1:G1000"), ">0")
Sheet2.Cells(i, 1).Value = i
If j > 0 Then
.Range(.Cells(1, 1), .Cells(j, 1)).Copy
Sheet2.Cells(i, 2).PasteSpecial Transpose:=True
End If
Next i
.Columns(7).ClearContents
.Range("A1:F1000").Sort Key1:=.Columns(1), Order1:=xlAscending
End With
End Sub

Alok Joshi

 

[Guest]

There are two issues with the code.

1. It will work if none of the two digit numbers is 00
2. It will have a problem is more than 255 instances of a number are found.

At a minimum you have to clarify those issues and what the code should do
and then someone may be able to help you.

Alok Joshi

 

[Guest]

Hello Alok and thank you.

First none of the numbers will be 00. They start with 01 and go through 99
Second the largest grouping by doing a countif shows that none have gone
over 150 instances.

I'm not exactly sure what the code should do as it was given to me but what
I would like it to do is have the numbers listed from row 1 through 99 and
then column A would be the number 01, 02, 03, etc. and Column B through
whatever the last column is (possibly 150 instances out) would have a number
representing where in the Sheet1 rows it fell so it would look like

01 25 27 33 45 50
02 10 13 26 33 42
03 05 15 26 38 105

etc.

Thank you for any assistance
David

 

[Guest]

Thank you everyone. setting it to "A1:F" corrected the error and let me
create the sheet2.

Everyone is very helpful.
Thank you.

David

Making it