Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error '1004'
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error '1004'
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error '1004'
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 "David" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error '1004'
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") -- Cheers Nigel "David" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error '1004'
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 In article , "David" wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error '1004'
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 "David" wrote: 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 "Alok" wrote: 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 "David" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error '1004'
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 "David" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error '1004'
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 "Alok" wrote: 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 "David" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error '1004'
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 "David" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run time error 1004, General ODBC error | New Users to Excel | |||
run time error 1004 | Excel Programming | |||
Run time error '1004': Generaol ODBC error | Excel Programming | |||
Run time error 1004 General ODCB Error | Excel Programming | |||
Application Run Time Error 1004 and Stack Error | Excel Programming |