Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error 1004
Hi all,
Have anyone experienced the following situation in VBA. Run time error:1004 Application-defined / object-defined error while run at the statement, dim DataSheet as worksheet Set DataSheet = ThisWorkbook.Sheets("Data") strPName = DataSheet.Cells.Offset(intRow, intCol) Have any ideas? Any help will be appreciated. Rgrds, Daniel |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error 1004
DataSheet.Cells.Offset(intRow, intCol)
looks odd. I would expect something more like strPName = DataSheet.Cells(1).Offset(intRow, intCol) "Cells" refers to the entire sheet. Tim "Daniel" wrote in message ... Hi all, Have anyone experienced the following situation in VBA. Run time error:1004 Application-defined / object-defined error while run at the statement, dim DataSheet as worksheet Set DataSheet = ThisWorkbook.Sheets("Data") strPName = DataSheet.Cells.Offset(intRow, intCol) Have any ideas? Any help will be appreciated. Rgrds, Daniel |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error 1004
Hi Daniel,
There are a number of problems with your code snippet. Firstly, you show four variables but you only dim one. It is good practice, and will stand you in good stead the more you program always to explicitly declare all variables. From its name, it would appear likely that the strPName variable represents a string. However, the right-hand side of the following strPName = DataSheet.Cells.Offset(intRow, intCol) is (an attempt) at a range object. I suspect, therefore, that you intended: strPName = DataSheet.Cells.Offset(intRow, intCol).Value The next, and most immediately pertinent, point concerns the offset expression: DataSheet.Cells returns the entire Datasheet worksheet and a worksheet cannot be offset. Presumably, it is a range on the Datasheet that you wish to offset. Lastly, your code gives no indication that the intRow and IntCol variables have been initialised, although you may well have done so in an undisclosed portion of your code. With all of these points addressed, I would anticipate your code reading something like: Sub Temp1() Dim DataSheet As Worksheet Dim strPName As String Dim intRow As Long, intCol As Long intRow = 1 intCol = 2 Set DataSheet = ThisWorkbook.Sheets("Data") strPName = DataSheet.Range("A10").Offset(intRow, intCol).Value End Sub This code ran without error in my tests. --- Regards, Norman "Daniel" wrote in message ... Hi all, Have anyone experienced the following situation in VBA. Run time error:1004 Application-defined / object-defined error while run at the statement, dim DataSheet as worksheet Set DataSheet = ThisWorkbook.Sheets("Data") strPName = DataSheet.Cells.Offset(intRow, intCol) Have any ideas? Any help will be appreciated. Rgrds, Daniel |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Runtime Error '1004' | Excel Discussion (Misc queries) | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) | |||
Runtime error 1004 | Excel Programming | |||
runtime error 1004 | Excel Programming | |||
Why I got this 1004 runtime error | Excel Programming |