Error without activating worksheet

R

Raj

Hi,

Please help me with the following code:

Sub TestforUniqueness()
Dim Cuniquecount As Long
Dim duniquecount As Long
Dim euniquecount As Long
Cuniquecount =
CountUniqueValues(ThisWorkbook.Worksheets("Report").Range(("c2"),
Range("c65536").End(xlUp)))
duniquecount =
CountUniqueValues(ThisWorkbook.Worksheets("Report").Range(("d2"),
Range("d65536").End(xlUp)))
euniquecount =
CountUniqueValues(ThisWorkbook.Worksheets("Report").Range(("e2"),
Range("e65536").End(xlUp)))
MsgBox Cuniquecount
MsgBox duniquecount
MsgBox euniquecount
End Sub

The workbook has two sheets: "Summary" and "Reports"
When the following code is run and the Active sheet is other than
Reports, it gives an application or object defined error. If add the
line "Worksheets("Reports").activate" then the code works well. Why
does this happen? Any way of doing this without activating the
worksheet.? (Before running this sub the earlier code writes some
values to the "Summary" sheet. But the summary sheet has not been
activated in the code before writing those values.

Thanks in advance for the help.

Regards,
Raj
 
J

Jim Rech

Your " Range("C65536").End(xlUp)) " is unmodified so it always refers to the
active sheet

With ThisWorkbook.Worksheets("Report")
Cuniquecount = CountUniqueValues(.Range(("c2"),
..Range("c65536").End(xlUp)))
duniquecount = CountUniqueValues(.Range(("d2"),
..Range("d65536").End(xlUp)))
euniquecount = CountUniqueValues(.Range(("e2"),
..Range("e65536").End(xlUp)))
End With


--
Jim
| Hi,
|
| Please help me with the following code:
|
| Sub TestforUniqueness()
| Dim Cuniquecount As Long
| Dim duniquecount As Long
| Dim euniquecount As Long
| Cuniquecount =
| CountUniqueValues(ThisWorkbook.Worksheets("Report").Range(("c2"),
| Range("c65536").End(xlUp)))
| duniquecount =
| CountUniqueValues(ThisWorkbook.Worksheets("Report").Range(("d2"),
| Range("d65536").End(xlUp)))
| euniquecount =
| CountUniqueValues(ThisWorkbook.Worksheets("Report").Range(("e2"),
| Range("e65536").End(xlUp)))
| MsgBox Cuniquecount
| MsgBox duniquecount
| MsgBox euniquecount
| End Sub
|
| The workbook has two sheets: "Summary" and "Reports"
| When the following code is run and the Active sheet is other than
| Reports, it gives an application or object defined error. If add the
| line "Worksheets("Reports").activate" then the code works well. Why
| does this happen? Any way of doing this without activating the
| worksheet.? (Before running this sub the earlier code writes some
| values to the "Summary" sheet. But the summary sheet has not been
| activated in the code before writing those values.
|
| Thanks in advance for the help.
|
| Regards,
| Raj
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top