Runtime error 1004

[Guest]

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

 

[Tim Williams]

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

 

[Norman Jones]

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.