| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
put value into cell after return from macro
I have a macro that calls another. When the second macro finishes, control
returns to the first. However, after the return, statements like "Cells(1,1) = 55" generate an error "Run time error '1004': Method 'Cells' of object '_Global' failled". I added a 'Sheets("sheet name").Select, and Sheets("sheet name").Activate but the error remained. Any suggestions will be appreciated. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
put value into cell after return from macro
Hi MG,
Post the problematic code. Where is the code housed? --- Regards, Norman "AltshulerMG" wrote in message ... I have a macro that calls another. When the second macro finishes, control returns to the first. However, after the return, statements like "Cells(1,1) = 55" generate an error "Run time error '1004': Method 'Cells' of object '_Global' failled". I added a 'Sheets("sheet name").Select, and Sheets("sheet name").Activate but the error remained. Any suggestions will be appreciated. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
put value into cell after return from macro
Taking your second question first, the code is in two macros in an Excel
file. I'm not sure if that answers your question. Regarding the code, I thought I explained it pretty well, however, here is a simplification of my macros: Sub One() Do some tasks here Two 'call subroutine Two ' when "Two" finishes, the code continues to execute here cells(1,1)=55 'this is the statement that generates the error End Sub _______________________________________________ Two do a bunch of things in different worksheets, ending up modifiying an embedded chart on the first worksheet (place where Macro One is called from). End sub "Norman Jones" wrote: Hi MG, Post the problematic code. Where is the code housed? --- Regards, Norman "AltshulerMG" wrote in message ... I have a macro that calls another. When the second macro finishes, control returns to the first. However, after the return, statements like "Cells(1,1) = 55" generate an error "Run time error '1004': Method 'Cells' of object '_Global' failled". I added a 'Sheets("sheet name").Select, and Sheets("sheet name").Activate but the error remained. Any suggestions will be appreciated. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
put value into cell after return from macro
Hi MG,
cells(1,1)=55 'this is the statement that generates the error The line above contains an unqualified reference to the Cells property. Excel treats such an unqualified reference as applying to the ActiveSheet or, if the code is housed in a sheet module, to the sheet holding the code. Without seeing your code, it is difficult to comment, but if, for example, the active sheet were not a worksheet (a chart sheet, perhaps), I could replicate your error. --- Regards, Norman "AltshulerMG" wrote in message ... Taking your second question first, the code is in two macros in an Excel file. I'm not sure if that answers your question. Regarding the code, I thought I explained it pretty well, however, here is a simplification of my macros: Sub One() Do some tasks here Two 'call subroutine Two ' when "Two" finishes, the code continues to execute here cells(1,1)=55 'this is the statement that generates the error End Sub _______________________________________________ Two do a bunch of things in different worksheets, ending up modifiying an embedded chart on the first worksheet (place where Macro One is called from). End sub "Norman Jones" wrote: Hi MG, Post the problematic code. Where is the code housed? --- Regards, Norman "AltshulerMG" wrote in message ... I have a macro that calls another. When the second macro finishes, control returns to the first. However, after the return, statements like "Cells(1,1) = 55" generate an error "Run time error '1004': Method 'Cells' of object '_Global' failled". I added a 'Sheets("sheet name").Select, and Sheets("sheet name").Activate but the error remained. Any suggestions will be appreciated. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
put value into cell after return from macro
it could be that your simplification is wrong or it could be that your code
is wrong. Post the proper code if you want help. The reference cells(1,1)=55 is wrong - this should be worksheetreference.cells(1,1).value = 55 where worksheetreference is a reference to the worksheet you are interested in. -- www.alignment-systems.com "AltshulerMG" wrote: Taking your second question first, the code is in two macros in an Excel file. I'm not sure if that answers your question. Regarding the code, I thought I explained it pretty well, however, here is a simplification of my macros: Sub One() Do some tasks here Two 'call subroutine Two ' when "Two" finishes, the code continues to execute here cells(1,1)=55 'this is the statement that generates the error End Sub _______________________________________________ Two do a bunch of things in different worksheets, ending up modifiying an embedded chart on the first worksheet (place where Macro One is called from). End sub "Norman Jones" wrote: Hi MG, Post the problematic code. Where is the code housed? --- Regards, Norman "AltshulerMG" wrote in message ... I have a macro that calls another. When the second macro finishes, control returns to the first. However, after the return, statements like "Cells(1,1) = 55" generate an error "Run time error '1004': Method 'Cells' of object '_Global' failled". I added a 'Sheets("sheet name").Select, and Sheets("sheet name").Activate but the error remained. Any suggestions will be appreciated. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
put value into cell after return from macro
I think you may be getting me on the right track. The last active item in
macro Two was an embedded Chart. I am trying to reference the worksheet itself. In other projects, I have been unable to make the worksheet active after making changes to the embedded chart. Do you know how I can do that? I think that will resolve my problem. "Norman Jones" wrote: Hi MG, cells(1,1)=55 'this is the statement that generates the error The line above contains an unqualified reference to the Cells property. Excel treats such an unqualified reference as applying to the ActiveSheet or, if the code is housed in a sheet module, to the sheet holding the code. Without seeing your code, it is difficult to comment, but if, for example, the active sheet were not a worksheet (a chart sheet, perhaps), I could replicate your error. --- Regards, Norman "AltshulerMG" wrote in message ... Taking your second question first, the code is in two macros in an Excel file. I'm not sure if that answers your question. Regarding the code, I thought I explained it pretty well, however, here is a simplification of my macros: Sub One() Do some tasks here Two 'call subroutine Two ' when "Two" finishes, the code continues to execute here cells(1,1)=55 'this is the statement that generates the error End Sub _______________________________________________ Two do a bunch of things in different worksheets, ending up modifiying an embedded chart on the first worksheet (place where Macro One is called from). End sub "Norman Jones" wrote: Hi MG, Post the problematic code. Where is the code housed? --- Regards, Norman "AltshulerMG" wrote in message ... I have a macro that calls another. When the second macro finishes, control returns to the first. However, after the return, statements like "Cells(1,1) = 55" generate an error "Run time error '1004': Method 'Cells' of object '_Global' failled". I added a 'Sheets("sheet name").Select, and Sheets("sheet name").Activate but the error remained. Any suggestions will be appreciated. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
put value into cell after return from macro
The reference may be wrong, but it works all over the place. Read my other
post to see what I think the problem is (last worksheet reference was to an embedded chart). I would like to try your (more complete) syntax, but I have not used the worksheetreference before and am not sure how to do this. If the worksheet is "Sheet_with_chart" what would the exact statement(s) be to implement worksheetreference.cells(1,1).value = 55 thanks very much "John.Greenan" wrote: it could be that your simplification is wrong or it could be that your code is wrong. Post the proper code if you want help. The reference cells(1,1)=55 is wrong - this should be worksheetreference.cells(1,1).value = 55 where worksheetreference is a reference to the worksheet you are interested in. -- www.alignment-systems.com "AltshulerMG" wrote: Taking your second question first, the code is in two macros in an Excel file. I'm not sure if that answers your question. Regarding the code, I thought I explained it pretty well, however, here is a simplification of my macros: Sub One() Do some tasks here Two 'call subroutine Two ' when "Two" finishes, the code continues to execute here cells(1,1)=55 'this is the statement that generates the error End Sub _______________________________________________ Two do a bunch of things in different worksheets, ending up modifiying an embedded chart on the first worksheet (place where Macro One is called from). End sub "Norman Jones" wrote: Hi MG, Post the problematic code. Where is the code housed? --- Regards, Norman "AltshulerMG" wrote in message ... I have a macro that calls another. When the second macro finishes, control returns to the first. However, after the return, statements like "Cells(1,1) = 55" generate an error "Run time error '1004': Method 'Cells' of object '_Global' failled". I added a 'Sheets("sheet name").Select, and Sheets("sheet name").Activate but the error remained. Any suggestions will be appreciated. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
put value into cell after return from macro
Hi MG,
I have been unable to make the worksheet active Sheets("YourSheetName").Activate However, it is rarely necessary to make physical selections band something like: Sheets("YourSheetName")..Cells(1,1). Value = 55 might well suffice. --- Regards, Norman "AltshulerMG" wrote in message ... I think you may be getting me on the right track. The last active item in macro Two was an embedded Chart. I am trying to reference the worksheet itself. In other projects, I have been unable to make the worksheet active after making changes to the embedded chart. Do you know how I can do that? I think that will resolve my problem. "Norman Jones" wrote: Hi MG, cells(1,1)=55 'this is the statement that generates the error The line above contains an unqualified reference to the Cells property. Excel treats such an unqualified reference as applying to the ActiveSheet or, if the code is housed in a sheet module, to the sheet holding the code. Without seeing your code, it is difficult to comment, but if, for example, the active sheet were not a worksheet (a chart sheet, perhaps), I could replicate your error. --- Regards, Norman "AltshulerMG" wrote in message ... Taking your second question first, the code is in two macros in an Excel file. I'm not sure if that answers your question. Regarding the code, I thought I explained it pretty well, however, here is a simplification of my macros: Sub One() Do some tasks here Two 'call subroutine Two ' when "Two" finishes, the code continues to execute here cells(1,1)=55 'this is the statement that generates the error End Sub _______________________________________________ Two do a bunch of things in different worksheets, ending up modifiying an embedded chart on the first worksheet (place where Macro One is called from). End sub "Norman Jones" wrote: Hi MG, Post the problematic code. Where is the code housed? --- Regards, Norman "AltshulerMG" wrote in message ... I have a macro that calls another. When the second macro finishes, control returns to the first. However, after the return, statements like "Cells(1,1) = 55" generate an error "Run time error '1004': Method 'Cells' of object '_Global' failled". I added a 'Sheets("sheet name").Select, and Sheets("sheet name").Activate but the error remained. Any suggestions will be appreciated. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
put value into cell after return from macro
Hi MG.
Sheets("Sheet_with_chart").Cells(1, 1) = 55 --- Regards, Norman "AltshulerMG" wrote in message ... The reference may be wrong, but it works all over the place. Read my other post to see what I think the problem is (last worksheet reference was to an embedded chart). I would like to try your (more complete) syntax, but I have not used the worksheetreference before and am not sure how to do this. If the worksheet is "Sheet_with_chart" what would the exact statement(s) be to implement worksheetreference.cells(1,1).value = 55 thanks very much "John.Greenan" wrote: it could be that your simplification is wrong or it could be that your code is wrong. Post the proper code if you want help. The reference cells(1,1)=55 is wrong - this should be worksheetreference.cells(1,1).value = 55 where worksheetreference is a reference to the worksheet you are interested in. -- www.alignment-systems.com "AltshulerMG" wrote: Taking your second question first, the code is in two macros in an Excel file. I'm not sure if that answers your question. Regarding the code, I thought I explained it pretty well, however, here is a simplification of my macros: Sub One() Do some tasks here Two 'call subroutine Two ' when "Two" finishes, the code continues to execute here cells(1,1)=55 'this is the statement that generates the error End Sub _______________________________________________ Two do a bunch of things in different worksheets, ending up modifiying an embedded chart on the first worksheet (place where Macro One is called from). End sub "Norman Jones" wrote: Hi MG, Post the problematic code. Where is the code housed? --- Regards, Norman "AltshulerMG" wrote in message ... I have a macro that calls another. When the second macro finishes, control returns to the first. However, after the return, statements like "Cells(1,1) = 55" generate an error "Run time error '1004': Method 'Cells' of object '_Global' failled". I added a 'Sheets("sheet name").Select, and Sheets("sheet name").Activate but the error remained. Any suggestions will be appreciated. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to return to original cell | Excel Discussion (Misc queries) | |||
| Macro-Return Blank Cell | Excel Discussion (Misc queries) | |||
| Macro - Return to starting cell | Excel Worksheet Functions | |||
| Need VBA macro to return active cell address (R1C1) | Excel Programming | |||
| Macro to loop thru sheets & return to a1 cell | Excel Programming | |||
Linear Mode
