Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with VBA runtime error
I get runtime error - 1004 Method 'Range' of object _Worksheet
failed during the Set sRange line. I even tried to hard code row/col values (next commented line) and get the same error. Please advise. Thanks! ' Get the info for the RESULTNAME block from ONE ' of the sheets; I'll be copying 4 sets of row:col ' data so I defined the rowArray(4) and colArray(4) Sub getResultInfo() Dim baseBook As Workbook Dim sheet1 As Worksheet ' destination Dim sheet2 As Worksheet ' source Dim sRange As Range ' source Dim dRange As Range ' dest Dim colVals(4) ' column numbers to be copied Dim destRow As Integer Dim destCol As Integer Const srcRow = 99 ' row number to be copied ' Brute force assignment of columns to ' be copied colVals(1) = 2 colVals(2) = 5 colVals(3) = 11 colVals(4) = 12 destRow = resRow + 1 ' resRow obtained in writeHeaders1() destCol = resCol + 1 Application.ScreenUpdating = False Set baseBook = ThisWorkbook Set sheet1 = baseBook.Sheets(1) Set sheet2 = baseBook.Sheets(2) 'Copy RESULTNAME Set sRange = sheet2.Range(Cells(srcRow, colVals(1)), _ Cells((srcRow + 16), colVals(1))) ' Set sRange = sheet2.Range(Cells(99, 2), Cells(114, 2)) Set dRange = sheet1.Range(Cells(destRow, destCol), _ Cells(destRow, (destCol + 16))) Call copyRange(sRange, dRange) End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with VBA runtime error
Tell it which sheet you want the cells from:
Set sRange = Sheet2.Range(Sheet2.Cells(srcRow, colVals(1)), _ Sheet2.Cells((srcRow + 16), colVals(1))) Cheers, Jason Lepack On Jan 17, 10:44 am, Zilla wrote: I get runtime error - 1004 Method 'Range' of object _Worksheet failed during the Set sRange line. I even tried to hard code row/col values (next commented line) and get the same error. Please advise. Thanks! ' Get the info for the RESULTNAME block from ONE ' of the sheets; I'll be copying 4 sets of row:col ' data so I defined the rowArray(4) and colArray(4) Sub getResultInfo() Dim baseBook As Workbook Dim sheet1 As Worksheet ' destination Dim sheet2 As Worksheet ' source Dim sRange As Range ' source Dim dRange As Range ' dest Dim colVals(4) ' column numbers to be copied Dim destRow As Integer Dim destCol As Integer Const srcRow = 99 ' row number to be copied ' Brute force assignment of columns to ' be copied colVals(1) = 2 colVals(2) = 5 colVals(3) = 11 colVals(4) = 12 destRow = resRow + 1 ' resRow obtained in writeHeaders1() destCol = resCol + 1 Application.ScreenUpdating = False Set baseBook = ThisWorkbook Set sheet1 = baseBook.Sheets(1) Set sheet2 = baseBook.Sheets(2) 'Copy RESULTNAME Set sRange = sheet2.Range(Cells(srcRow, colVals(1)), _ Cells((srcRow + 16), colVals(1))) ' Set sRange = sheet2.Range(Cells(99, 2), Cells(114, 2)) Set dRange = sheet1.Range(Cells(destRow, destCol), _ Cells(destRow, (destCol + 16))) Call copyRange(sRange, dRange) End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with VBA runtime error
But doesn't the "sheet2.Range()" call effectively do that since it
refers to the sheet2 object? Anyway the call worked by "activating" the sheets first before calling the Range method. On Jan 17, 11:00*am, Jason Lepack wrote: Tell it which sheet you want the cells from: Set sRange = Sheet2.Range(Sheet2.Cells(srcRow, colVals(1)), _ * * * * * * * * * * * * * * * Sheet2.Cells((srcRow + 16), colVals(1))) Cheers, Jason Lepack On Jan 17, 10:44 am, Zilla wrote: I get runtime error - 1004 Method 'Range' of object _Worksheet failed during the Set sRange line. I even tried to hard code row/col values (next commented line) and get the same error. Please advise. Thanks! ' Get the info for the RESULTNAME block from ONE ' of the sheets; I'll be copying 4 sets of row:col ' data so I defined the rowArray(4) and colArray(4) Sub getResultInfo() * * Dim baseBook As Workbook * * Dim sheet1 As Worksheet ' destination * * Dim sheet2 As Worksheet ' source * * Dim sRange As Range ' source * * Dim dRange As Range ' dest * * Dim colVals(4) ' column numbers to be copied * * Dim destRow As Integer * * Dim destCol As Integer * * Const srcRow = 99 ' row number to be copied * * ' Brute force assignment of columns to * * ' be copied * * colVals(1) = 2 * * colVals(2) = 5 * * colVals(3) = 11 * * colVals(4) = 12 * * destRow = resRow + 1 ' resRow obtained in writeHeaders1() * * destCol = resCol + 1 * * Application.ScreenUpdating = False * * Set baseBook = ThisWorkbook * * Set sheet1 = baseBook.Sheets(1) * * Set sheet2 = baseBook.Sheets(2) * * 'Copy RESULTNAME * * Set sRange = sheet2.Range(Cells(srcRow, colVals(1)), _ * * * * * * * * * * * * * * * Cells((srcRow + 16), colVals(1))) * * ' Set sRange = sheet2.Range(Cells(99, 2), Cells(114, 2)) * * Set dRange = sheet1.Range(Cells(destRow, destCol), _ * * * * * * * * * * * * * * * Cells(destRow, (destCol + 16))) * * Call copyRange(sRange, dRange) End Sub- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with VBA runtime error
Any range object such as Range or Cell that is not explicitly referenced to a
sheet will default to the active sheet. What you have is equivalent to... Set sRange = sheet2.Range(Activesheet.Cells(srcRow, colVals(1)), _ Activesheet.Cells((srcRow + 16), colVals(1))) Which is obviously wrong. By activating the sheet prior to calling this line your active sheet is sheet 2 so it works. That being said I would still be inclined to go with Jason's suggested code as it works regardless of which sheet is active. Or a little cleaner and a tad more efficient would be. With sheet2 Set sRange = .Range(.Cells(srcRow, colVals(1)), _ .Cells((srcRow + 16), colVals(1))) End with -- HTH... Jim Thomlinson "Zilla" wrote: But doesn't the "sheet2.Range()" call effectively do that since it refers to the sheet2 object? Anyway the call worked by "activating" the sheets first before calling the Range method. On Jan 17, 11:00 am, Jason Lepack wrote: Tell it which sheet you want the cells from: Set sRange = Sheet2.Range(Sheet2.Cells(srcRow, colVals(1)), _ Sheet2.Cells((srcRow + 16), colVals(1))) Cheers, Jason Lepack On Jan 17, 10:44 am, Zilla wrote: I get runtime error - 1004 Method 'Range' of object _Worksheet failed during the Set sRange line. I even tried to hard code row/col values (next commented line) and get the same error. Please advise. Thanks! ' Get the info for the RESULTNAME block from ONE ' of the sheets; I'll be copying 4 sets of row:col ' data so I defined the rowArray(4) and colArray(4) Sub getResultInfo() Dim baseBook As Workbook Dim sheet1 As Worksheet ' destination Dim sheet2 As Worksheet ' source Dim sRange As Range ' source Dim dRange As Range ' dest Dim colVals(4) ' column numbers to be copied Dim destRow As Integer Dim destCol As Integer Const srcRow = 99 ' row number to be copied ' Brute force assignment of columns to ' be copied colVals(1) = 2 colVals(2) = 5 colVals(3) = 11 colVals(4) = 12 destRow = resRow + 1 ' resRow obtained in writeHeaders1() destCol = resCol + 1 Application.ScreenUpdating = False Set baseBook = ThisWorkbook Set sheet1 = baseBook.Sheets(1) Set sheet2 = baseBook.Sheets(2) 'Copy RESULTNAME Set sRange = sheet2.Range(Cells(srcRow, colVals(1)), _ Cells((srcRow + 16), colVals(1))) ' Set sRange = sheet2.Range(Cells(99, 2), Cells(114, 2)) Set dRange = sheet1.Range(Cells(destRow, destCol), _ Cells(destRow, (destCol + 16))) Call copyRange(sRange, dRange) End Sub- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with VBA runtime error
As I see that Jim Thomlinson has replied, I'll echo his statements.
In Jim's example: Set sRange = sheet2.Range(Activesheet.Cells(srcRow, colVals(1)), _ Activesheet.Cells((srcRow + 16), colVals(1))) The reason you get the error is that you are mixing contexts. You are attempting to select a range on Sheet2 using cells that are on Sheet1. Cheers, Jason Lepack On Jan 17, 11:26 am, Zilla wrote: But doesn't the "sheet2.Range()" call effectively do that since it refers to the sheet2 object? Anyway the call worked by "activating" the sheets first before calling the Range method. On Jan 17, 11:00 am, Jason Lepack wrote: Tell it which sheet you want the cells from: Set sRange = Sheet2.Range(Sheet2.Cells(srcRow, colVals(1)), _ Sheet2.Cells((srcRow + 16), colVals(1))) Cheers, Jason Lepack On Jan 17, 10:44 am, Zilla wrote: I get runtime error - 1004 Method 'Range' of object _Worksheet failed during the Set sRange line. I even tried to hard code row/col values (next commented line) and get the same error. Please advise. Thanks! ' Get the info for the RESULTNAME block from ONE ' of the sheets; I'll be copying 4 sets of row:col ' data so I defined the rowArray(4) and colArray(4) Sub getResultInfo() Dim baseBook As Workbook Dim sheet1 As Worksheet ' destination Dim sheet2 As Worksheet ' source Dim sRange As Range ' source Dim dRange As Range ' dest Dim colVals(4) ' column numbers to be copied Dim destRow As Integer Dim destCol As Integer Const srcRow = 99 ' row number to be copied ' Brute force assignment of columns to ' be copied colVals(1) = 2 colVals(2) = 5 colVals(3) = 11 colVals(4) = 12 destRow = resRow + 1 ' resRow obtained in writeHeaders1() destCol = resCol + 1 Application.ScreenUpdating = False Set baseBook = ThisWorkbook Set sheet1 = baseBook.Sheets(1) Set sheet2 = baseBook.Sheets(2) 'Copy RESULTNAME Set sRange = sheet2.Range(Cells(srcRow, colVals(1)), _ Cells((srcRow + 16), colVals(1))) ' Set sRange = sheet2.Range(Cells(99, 2), Cells(114, 2)) Set dRange = sheet1.Range(Cells(destRow, destCol), _ Cells(destRow, (destCol + 16))) Call copyRange(sRange, dRange) End Sub- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with VBA runtime error
As an aside, the line...
Set sheet1 = baseBook.Sheets(1) ....could be problematic, if Sheets(1) happens to be a chart sheet. (Remember, "Sheets" is generic for all types of sheets in a workbook, i.e. worksheet, chart, macro, etc.) I think it is better to use something like the following, unless you haven't bothered to set the tab names on your worksheets: Set sheet1 = baseBook.Worksheets("YourWorksheetName") Also, I generally use an object variable like "wsData" to refer to a worksheet, rather than "sheet1". "sheet1" might be conflicting with the CodeName assigned by Excel when the sheet was originally inserted into the workbook (see the Project Explorer). This might cause problems (name conflicts) after lots of extensive editing has been done and the symbol tables have become large. So, I would change your code to the following 2 declarations: Dim wsDestination As Worksheet ' destination Dim wsSource As Worksheet ' source -- Regards, Bill Renaud |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
runtime error '1004' application or object defined error | Excel Programming | |||
xpath error? Runtime Error 13 type mismatch | Excel Discussion (Misc queries) | |||
runtime error '1004' application or object defined error. Please help | Excel Programming | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) | |||
Syntax Error Runtime Error '424' Object Required | Excel Programming |