Excel Error Help!

[lehainam]

Dear all,

I have a code in Excel and whenever I run the program there is an error:

Method 'Cells' of object '_Worksheet' failed

and error number is -2147417848

Could any one can help me or if I have the error number how can I get help on line (which website I can access)

Thanks a lot!

Nam

[Mangesh Yadav]

you are using the Cells without the parent worksheet name. Use it as

Worksheets("Sheet1").Cells

Mangesh



"lehainam" <[email protected]> wrote in
message news:[email protected]...
>
> Dear all,
>
> I have a code in Excel and whenever I run the program there is an
> error:
>
> Method 'Cells' of object '_Worksheet' failed
>
> and error number is -2147417848
>
> Could any one can help me or if I have the error number how can I get
> help on line (which website I can access)
>
> Thanks a lot!
>
> Nam
>
>
> --
> lehainam
> ------------------------------------------------------------------------
> lehainam's Profile:
http://www.excelforum.com/member.php...o&userid=18615
> View this thread: http://www.excelforum.com/showthread...hreadid=395524
>

[Norman Jones]

Hi Mangesh,

> you are using the Cells without the parent worksheet name. Use it as
>
> Worksheets("Sheet1").Cells

If Cells were used without explicit qualification, it would represent a
range object referring to all the cells on the active sheet. Therefore, lack
of qualification should not, per se, cause an error.

If the active sheet were not a worksheet, then I would anticipate a 1004
run-time error rather than the OP's reported error.


---
Regards,
Norman



"Mangesh Yadav" <[email protected]> wrote in message
news:[email protected]...
> you are using the Cells without the parent worksheet name. Use it as
>
> Worksheets("Sheet1").Cells
>
> Mangesh
>
>
>
> "lehainam" <[email protected]> wrote
> in
> message news:[email protected]...
>>
>> Dear all,
>>
>> I have a code in Excel and whenever I run the program there is an
>> error:
>>
>> Method 'Cells' of object '_Worksheet' failed
>>
>> and error number is -2147417848
>>
>> Could any one can help me or if I have the error number how can I get
>> help on line (which website I can access)
>>
>> Thanks a lot!
>>
>> Nam
>>
>>
>> --
>> lehainam
>> ------------------------------------------------------------------------
>> lehainam's Profile:
> http://www.excelforum.com/member.php...o&userid=18615
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=395524
>>
>
>

[Norman Jones]

Hi Lehainam,

Please post your problematic code.


---
Regards,
Norman



"lehainam" <[email protected]> wrote in
message news:[email protected]...
>
> Dear all,
>
> I have a code in Excel and whenever I run the program there is an
> error:
>
> Method 'Cells' of object '_Worksheet' failed
>
> and error number is -2147417848
>
> Could any one can help me or if I have the error number how can I get
> help on line (which website I can access)
>
> Thanks a lot!
>
> Nam
>
>
> --
> lehainam
> ------------------------------------------------------------------------
> lehainam's Profile:
> http://www.excelforum.com/member.php...o&userid=18615
> View this thread: http://www.excelforum.com/showthread...hreadid=395524
>

[Bob Phillips]

If it is not what Mangesh suggests, post the code for us to see.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mangesh Yadav" <[email protected]> wrote in message
news:[email protected]...
> you are using the Cells without the parent worksheet name. Use it as
>
> Worksheets("Sheet1").Cells
>
> Mangesh
>
>
>
> "lehainam" <[email protected]> wrote
in
> message news:[email protected]...
> >
> > Dear all,
> >
> > I have a code in Excel and whenever I run the program there is an
> > error:
> >
> > Method 'Cells' of object '_Worksheet' failed
> >
> > and error number is -2147417848
> >
> > Could any one can help me or if I have the error number how can I get
> > help on line (which website I can access)
> >
> > Thanks a lot!
> >
> > Nam
> >
> >
> > --
> > lehainam
> > ------------------------------------------------------------------------
> > lehainam's Profile:
> http://www.excelforum.com/member.php...o&userid=18615
> > View this thread:
http://www.excelforum.com/showthread...hreadid=395524
> >
>
>

[Mangesh Yadav]

Hi Norman,

quite true indeed. I did a small test, and with a chart as the active sheet
and using the Cells() without the parent sheet, I get a run time error 1004,
method cells of object global failed.

Thanks.

Mangesh




"Norman Jones" <[email protected]> wrote in message
news:e8SzoY#[email protected]...
> Hi Mangesh,
>
> > you are using the Cells without the parent worksheet name. Use it as
> >
> > Worksheets("Sheet1").Cells
>
> If Cells were used without explicit qualification, it would represent a
> range object referring to all the cells on the active sheet. Therefore,
lack
> of qualification should not, per se, cause an error.
>
> If the active sheet were not a worksheet, then I would anticipate a 1004
> run-time error rather than the OP's reported error.
>
>
> ---
> Regards,
> Norman
>
>
>
> "Mangesh Yadav" <[email protected]> wrote in message
> news:[email protected]...
> > you are using the Cells without the parent worksheet name. Use it as
> >
> > Worksheets("Sheet1").Cells
> >
> > Mangesh
> >
> >
> >
> > "lehainam" <[email protected]> wrote
> > in
> > message news:[email protected]...
> >>
> >> Dear all,
> >>
> >> I have a code in Excel and whenever I run the program there is an
> >> error:
> >>
> >> Method 'Cells' of object '_Worksheet' failed
> >>
> >> and error number is -2147417848
> >>
> >> Could any one can help me or if I have the error number how can I get
> >> help on line (which website I can access)
> >>
> >> Thanks a lot!
> >>
> >> Nam
> >>
> >>
> >> --
> >> lehainam
>
>> ------------------------------------------------------------------------
> >> lehainam's Profile:
> > http://www.excelforum.com/member.php...o&userid=18615
> >> View this thread:
> >> http://www.excelforum.com/showthread...hreadid=395524
> >>
> >
> >
>
>

[lehainam]

Dear all,

Thanks for your help.

Please find the attached file for your reference.

In this Workbook, I am standing in 'JI' sheet I click 'Save Data' to copy data from this worksheet to 'Data' worksheet. The error will occur.

Your help is appreciated.

Thanks a lot!

Nam

[Norman Jones]

Hi Lehainam,

Few in this newsgroup will open attachments.

Post your code and indicate where the error is occurring in the code.


---
Regards,
Norman



"lehainam" <[email protected]> wrote in
message news:[email protected]...
>
> Dear all,
>
> Thanks for your help.
>
> Please find the attached file for your reference.
>
> In this Workbook, I am standing in 'JI' sheet I click 'Save Data' to
> copy data from this worksheet to 'Data' worksheet. The error will
> occur.
>
> Your help is appreciated.
>
> Thanks a lot!
>
> Nam
>
>
> --
> lehainam
> ------------------------------------------------------------------------
> lehainam's Profile:
> http://www.excelforum.com/member.php...o&userid=18615
> View this thread: http://www.excelforum.com/showthread...hreadid=395524
>

[lehainam]

Dear all,

Below is the code with error: Method 'Cells' of object '_Worksheet' failed

Sub SaveData()
On Error GoTo SaveData_Err
Dim strJType
Dim x As Integer, i As Integer, j As Integer, m As Integer
Dim n As Integer, r As Integer, c As Integer
Sheets("Data").Activate

m = Range("A65536").End(xlUp).Row
If m <= 3 Then m = 3
Sheets("JI").Activate
strJType = Cells(34, 2)
x = Range("C65536").End(xlUp).Row
For i = 34 To x
j = (i - 33) * 2 + 2 + (m - 3)

'Trans. Ref
Sheets("Data").Cells(j, 1) = Range("J9")
Sheets("Data").Cells(j + 1, 1) = Range("J9")

'Period
Sheets("Data").Cells(j, 2) = Range("E29")
Sheets("Data").Cells(j + 1, 2) = Range("E29")

'Tran Date
Sheets("Data").Cells(j, 3) = Range("E30")
Sheets("Data").Cells(j + 1, 3) = Range("E30")

'Dr/Cr
Sheets("Data").Cells(j, 8) = "D"
Sheets("Data").Cells(j + 1, 8) = "C"

'JT
Sheets("Data").Cells(j, 10) = strJType
Sheets("Data").Cells(j + 1, 10) = strJType

'AC Code
Sheets("Data").Cells(j, 4) = Cells(i, 3)
Sheets("Data").Cells(j + 1, 4) = Cells(i, 5)

'T0 Code
Sheets("Data").Cells(j, 11) = Cells(i, 4)
Sheets("Data").Cells(j + 1, 11) = Cells(i, 4)

'Description
Sheets("Data").Cells(j, 9) = Cells(i, 6)
Sheets("Data").Cells(j + 1, 9) = Cells(i, 6)

'T1
Sheets("Data").Cells(j, 12) = Cells(i, 7)
Sheets("Data").Cells(j + 1, 12) = Cells(i, 7)

'T3
Sheets("Data").Cells(j, 14) = Cells(i, 8)
Sheets("Data").Cells(j + 1, 14) = Cells(i, 8)

'T4
Sheets("Data").Cells(j, 15) = Cells(i, 9)
Sheets("Data").Cells(j + 1, 15) = Cells(i, 9)

'T5
Sheets("Data").Cells(j, 16) = Cells(i, 10)
Sheets("Data").Cells(j + 1, 16) = Cells(i, 10)

'Amt
Sheets("Data").Cells(j, 5) = Cells(i, 11)
Sheets("Data").Cells(j + 1, 5) = Cells(i, 11)
Next

'Save Description
For n = 17 To 24
r = m + 1 'row
c = (n - 16) + 24 'Begin with column Y
Sheets("Data").Cells(r, c) = Cells(n, 3)
Sheets("Data").Cells(r + 1, c) = Cells(n, 11)
Next
Sheets("Data").Activate
Cells(m + 1, 1).Select
'MsgBox x
Exit Sub
SaveData_Err:
MsgBox Err.Description
Exit Sub
End Sub

Thanks a lot for your help!

Nam

[Norman Jones]

Hi Lehainam,

Your code ran without problem for me and I was unable to reproduce your
error.

I would suggest that you download Rob Bovey's Code Cleaner Addin and retry
your code after running the addin.

The Code Cleaner addin is freely downloadable at:

http://www.appspro.com/Utilities/Utilities.htm

The downloaded EXE file initiates an automatic installation process. Once
loaded, the Code Cleaner can be called from the Tools menu.

---
Regards,
Norman



"lehainam" <[email protected]> wrote in
message news:[email protected]...
>
> Dear all,
>
> Below is the code with error: Method 'Cells' of object '_Worksheet'
> failed
>
> Sub SaveData()
> On Error GoTo SaveData_Err
> Dim strJType
> Dim x As Integer, i As Integer, j As Integer, m As Integer
> Dim n As Integer, r As Integer, c As Integer
> Sheets("Data").Activate
>
> m = Range("A65536").End(xlUp).Row
> If m <= 3 Then m = 3
> Sheets("JI").Activate
> strJType = Cells(34, 2)
> x = Range("C65536").End(xlUp).Row
> For i = 34 To x
> j = (i - 33) * 2 + 2 + (m - 3)
>
> 'Trans. Ref
> Sheets("Data").Cells(j, 1) = Range("J9")
> Sheets("Data").Cells(j + 1, 1) = Range("J9")
>
> 'Period
> Sheets("Data").Cells(j, 2) = Range("E29")
> Sheets("Data").Cells(j + 1, 2) = Range("E29")
>
> 'Tran Date
> Sheets("Data").Cells(j, 3) = Range("E30")
> Sheets("Data").Cells(j + 1, 3) = Range("E30")
>
> 'Dr/Cr
> Sheets("Data").Cells(j, 8) = "D"
> Sheets("Data").Cells(j + 1, 8) = "C"
>
> 'JT
> Sheets("Data").Cells(j, 10) = strJType
> Sheets("Data").Cells(j + 1, 10) = strJType
>
> 'AC Code
> Sheets("Data").Cells(j, 4) = Cells(i, 3)
> Sheets("Data").Cells(j + 1, 4) = Cells(i, 5)
>
> 'T0 Code
> Sheets("Data").Cells(j, 11) = Cells(i, 4)
> Sheets("Data").Cells(j + 1, 11) = Cells(i, 4)
>
> 'Description
> Sheets("Data").Cells(j, 9) = Cells(i, 6)
> Sheets("Data").Cells(j + 1, 9) = Cells(i, 6)
>
> 'T1
> Sheets("Data").Cells(j, 12) = Cells(i, 7)
> Sheets("Data").Cells(j + 1, 12) = Cells(i, 7)
>
> 'T3
> Sheets("Data").Cells(j, 14) = Cells(i, 8)
> Sheets("Data").Cells(j + 1, 14) = Cells(i, 8)
>
> 'T4
> Sheets("Data").Cells(j, 15) = Cells(i, 9)
> Sheets("Data").Cells(j + 1, 15) = Cells(i, 9)
>
> 'T5
> Sheets("Data").Cells(j, 16) = Cells(i, 10)
> Sheets("Data").Cells(j + 1, 16) = Cells(i, 10)
>
> 'Amt
> Sheets("Data").Cells(j, 5) = Cells(i, 11)
> Sheets("Data").Cells(j + 1, 5) = Cells(i, 11)
> Next
>
> 'Save Description
> For n = 17 To 24
> r = m + 1 'row
> c = (n - 16) + 24 'Begin with column Y
> Sheets("Data").Cells(r, c) = Cells(n, 3)
> Sheets("Data").Cells(r + 1, c) = Cells(n, 11)
> Next
> Sheets("Data").Activate
> Cells(m + 1, 1).Select
> 'MsgBox x
> Exit Sub
> SaveData_Err:
> MsgBox Err.Description
> Exit Sub
> End Sub
>
> Thanks a lot for your help!
>
> Nam
>
>
> --
> lehainam
> ------------------------------------------------------------------------
> lehainam's Profile:
> http://www.excelforum.com/member.php...o&userid=18615
> View this thread: http://www.excelforum.com/showthread...hreadid=395524
>