Error without activating worksheet

[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

 

[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