| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
What do I not see?
The following code excutes as shown, however the 2nd Range Select statement
receives run time error '1004'. When I manually select A1 of sheet "data" and press cntl + end, cell "U53" is selected. What am I overlooking? TIA, Lee Set rnge = wb.Sheets("MoreStats").Range("A1") Range(rnge, rnge.End(xlDown).End(xlToRight)).Select Selection.clear Sheets("data").Select Set rnge = wb.Sheets("data").Range("A1") Msgbox rnge.address 'Shows "$A$1 Range(rnge, rnge.End(xlDown).End(xlToRight)).Select '*** This one fails Selection.copy |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What do I not see?
Selects are problematic and there is really no need for them in this case (in
most cases actually)... Try something more like this Set rnge = wb.Sheets("MoreStats").Range("A1") Range(rnge, rnge.End(xlDown).End(xlToRight).clear Set rnge = wb.Sheets("data").Range("A1") Msgbox rnge.address 'Shows "$A$1 Range(rnge, rnge.End(xlDown).End(xlToRight)).Copy -- HTH... Jim Thomlinson "Lee Hunter" wrote: The following code excutes as shown, however the 2nd Range Select statement receives run time error '1004'. When I manually select A1 of sheet "data" and press cntl + end, cell "U53" is selected. What am I overlooking? TIA, Lee Set rnge = wb.Sheets("MoreStats").Range("A1") Range(rnge, rnge.End(xlDown).End(xlToRight)).Select Selection.clear Sheets("data").Select Set rnge = wb.Sheets("data").Range("A1") Msgbox rnge.address 'Shows "$A$1 Range(rnge, rnge.End(xlDown).End(xlToRight)).Select '*** This one fails Selection.copy |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What do I not see?
Typo Jim
Set rnge = wb.Sheets("MoreStats").Range("A1") Range(rnge, rnge.End(xlDown).End(xlToRight)).clear Set rnge = wb.Sheets("data").Range("A1") Msgbox rnge.address 'Shows "$A$1 Range(rnge, rnge.End(xlDown).End(xlToRight)).Copy -- HTH RP (remove nothere from the email address if mailing direct) "Jim Thomlinson" wrote in message ... Selects are problematic and there is really no need for them in this case (in most cases actually)... Try something more like this Set rnge = wb.Sheets("MoreStats").Range("A1") Range(rnge, rnge.End(xlDown).End(xlToRight).clear Set rnge = wb.Sheets("data").Range("A1") Msgbox rnge.address 'Shows "$A$1 Range(rnge, rnge.End(xlDown).End(xlToRight)).Copy -- HTH... Jim Thomlinson "Lee Hunter" wrote: The following code excutes as shown, however the 2nd Range Select statement receives run time error '1004'. When I manually select A1 of sheet "data" and press cntl + end, cell "U53" is selected. What am I overlooking? TIA, Lee Set rnge = wb.Sheets("MoreStats").Range("A1") Range(rnge, rnge.End(xlDown).End(xlToRight)).Select Selection.clear Sheets("data").Select Set rnge = wb.Sheets("data").Range("A1") Msgbox rnge.address 'Shows "$A$1 Range(rnge, rnge.End(xlDown).End(xlToRight)).Select '*** This one fails Selection.copy |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What do I not see?
Jim,
How nice and clean that is. However, here is the revised code, which now fails with the same run time error, this time on the copy statement. An further thoughts? Lee Sheets("Morestats").Activate Sheets("Morestats").Select Set rnge = wb.Sheets("MoreStats").Range("A1") Range(rnge, rnge.End(xlDown).End(xlToRight)).clear Sheets("data").Activate Sheets("data").Select Set rnge = wb.Sheets("data").Range("A1") Range(rnge, rnge.End(xlDown).End(xlToRight)).Copy **** run time error '1004' "Jim Thomlinson" wrote: Selects are problematic and there is really no need for them in this case (in most cases actually)... Try something more like this Set rnge = wb.Sheets("MoreStats").Range("A1") Range(rnge, rnge.End(xlDown).End(xlToRight).clear Set rnge = wb.Sheets("data").Range("A1") Msgbox rnge.address 'Shows "$A$1 Range(rnge, rnge.End(xlDown).End(xlToRight)).Copy -- HTH... Jim Thomlinson "Lee Hunter" wrote: The following code excutes as shown, however the 2nd Range Select statement receives run time error '1004'. When I manually select A1 of sheet "data" and press cntl + end, cell "U53" is selected. What am I overlooking? TIA, Lee Set rnge = wb.Sheets("MoreStats").Range("A1") Range(rnge, rnge.End(xlDown).End(xlToRight)).Select Selection.clear Sheets("data").Select Set rnge = wb.Sheets("data").Range("A1") Msgbox rnge.address 'Shows "$A$1 Range(rnge, rnge.End(xlDown).End(xlToRight)).Select '*** This one fails Selection.copy |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What do I not see?
Lets clean this up if we can. Without the typos this time :)
dim wksStats as worksheet dim wksData as worksheet dim rngStats as range dim rngData as range set wksStats = wb.Sheets("Morestats") set wksData = wb.Sheets("Data") with wksStats set rngStats = .range(.range("A1"), .Range("A1").end(xldown).end(xlToRight)) rngstats.clear end with with wksData set rngData = .range(.range("A1"), .Range("A1").end(xldown).end(xlToRight)) rngData.copy end with -- HTH... Jim Thomlinson "Lee Hunter" wrote: Jim, How nice and clean that is. However, here is the revised code, which now fails with the same run time error, this time on the copy statement. An further thoughts? Lee Sheets("Morestats").Activate Sheets("Morestats").Select Set rnge = wb.Sheets("MoreStats").Range("A1") Range(rnge, rnge.End(xlDown).End(xlToRight)).clear Sheets("data").Activate Sheets("data").Select Set rnge = wb.Sheets("data").Range("A1") Range(rnge, rnge.End(xlDown).End(xlToRight)).Copy **** run time error '1004' "Jim Thomlinson" wrote: Selects are problematic and there is really no need for them in this case (in most cases actually)... Try something more like this Set rnge = wb.Sheets("MoreStats").Range("A1") Range(rnge, rnge.End(xlDown).End(xlToRight).clear Set rnge = wb.Sheets("data").Range("A1") Msgbox rnge.address 'Shows "$A$1 Range(rnge, rnge.End(xlDown).End(xlToRight)).Copy -- HTH... Jim Thomlinson "Lee Hunter" wrote: The following code excutes as shown, however the 2nd Range Select statement receives run time error '1004'. When I manually select A1 of sheet "data" and press cntl + end, cell "U53" is selected. What am I overlooking? TIA, Lee Set rnge = wb.Sheets("MoreStats").Range("A1") Range(rnge, rnge.End(xlDown).End(xlToRight)).Select Selection.clear Sheets("data").Select Set rnge = wb.Sheets("data").Range("A1") Msgbox rnge.address 'Shows "$A$1 Range(rnge, rnge.End(xlDown).End(xlToRight)).Select '*** This one fails Selection.copy |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What do I not see?
Thanks Jim,
Even cleaner and neater. Did the trick. Much appreciated. Lee "Jim Thomlinson" wrote: Lets clean this up if we can. Without the typos this time :) dim wksStats as worksheet dim wksData as worksheet dim rngStats as range dim rngData as range set wksStats = wb.Sheets("Morestats") set wksData = wb.Sheets("Data") with wksStats set rngStats = .range(.range("A1"), .Range("A1").end(xldown).end(xlToRight)) rngstats.clear end with with wksData set rngData = .range(.range("A1"), .Range("A1").end(xldown).end(xlToRight)) rngData.copy end with -- HTH... Jim Thomlinson "Lee Hunter" wrote: Jim, How nice and clean that is. However, here is the revised code, which now fails with the same run time error, this time on the copy statement. An further thoughts? Lee Sheets("Morestats").Activate Sheets("Morestats").Select Set rnge = wb.Sheets("MoreStats").Range("A1") Range(rnge, rnge.End(xlDown).End(xlToRight)).clear Sheets("data").Activate Sheets("data").Select Set rnge = wb.Sheets("data").Range("A1") Range(rnge, rnge.End(xlDown).End(xlToRight)).Copy **** run time error '1004' "Jim Thomlinson" wrote: Selects are problematic and there is really no need for them in this case (in most cases actually)... Try something more like this Set rnge = wb.Sheets("MoreStats").Range("A1") Range(rnge, rnge.End(xlDown).End(xlToRight).clear Set rnge = wb.Sheets("data").Range("A1") Msgbox rnge.address 'Shows "$A$1 Range(rnge, rnge.End(xlDown).End(xlToRight)).Copy -- HTH... Jim Thomlinson "Lee Hunter" wrote: The following code excutes as shown, however the 2nd Range Select statement receives run time error '1004'. When I manually select A1 of sheet "data" and press cntl + end, cell "U53" is selected. What am I overlooking? TIA, Lee Set rnge = wb.Sheets("MoreStats").Range("A1") Range(rnge, rnge.End(xlDown).End(xlToRight)).Select Selection.clear Sheets("data").Select Set rnge = wb.Sheets("data").Range("A1") Msgbox rnge.address 'Shows "$A$1 Range(rnge, rnge.End(xlDown).End(xlToRight)).Select '*** This one fails Selection.copy |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
