Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error without activating worksheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error without activating worksheet
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 "Raj" wrote in message ... | 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Activating a Worksheet? | Excel Discussion (Misc queries) | |||
Worksheet activating | Excel Programming | |||
Activating a worksheet | Excel Programming | |||
Error on activating a window a open workbook | Excel Programming | |||
Error 40036 when activating a sheet | Excel Programming |