Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing cell contents automatically
Not exactly one click, but in the with a single cell selected, do
Edit=goto=special and select Constants and Numbers, then OK, then delete. If this results in too much being deleted, you can select a more appropriate range and do it. Assumes manual inputs are numbers. If text is included, then don't restrict the selection to numbers. I am sure the transfer could be easily coded in a macro, but without knowing the layout of the sheet and what you want to transfer and where, it would be hard to provide anything specific. Sub TransData() dim i as long, mysource as variant, mydest as variant dim myclear as variant mysource = Array("A1","B9","C11","G13") myDest = Array("M6","R13","Z26","B4") for i = lbound(mysource) to ubound(mysource) Range(mydest(i)).Value = Range(mysource(i)).Value Range(mysource(i)).ClearContents Next ' option - clear manual inputs myclear = Array(A3,B5:B30,R11,M31) for i = lbound(myclear) to ubound(myclear) Range(myclear(i)).ClearContents Next ' or ' Range("A3,B5:B30,R11,M31").ClearContents end Sub so fill in the appropriate cells in each array. mysource and mydest must have the same number of elements. -- Regards, Tom Ogilvy "Jun" wrote in message ... I have an Excel file with three worksheets which makes cross references to each other to get certain values. It is basically a simple weekly finace sheet. I was wondering if there was an easy one click way of deleting the contents of only those cells which require manual input each week. I am also looking to transfer the current week's figures into last week's figures cell ready for use the following week. Any hints and tips would be gratefully received. Many thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing cell contents automatically
Thank you for your advice. I will try it out. Is there a
way of creating a button on the worksheet which can then execute the definition I create for the cells I want the contents deleted? I have created a macro to do it but am not sure if that can then be attached to a button. Thanks again. Jun -----Original Message----- Not exactly one click, but in the with a single cell selected, do Edit=goto=special and select Constants and Numbers, then OK, then delete. If this results in too much being deleted, you can select a more appropriate range and do it. Assumes manual inputs are numbers. If text is included, then don't restrict the selection to numbers. I am sure the transfer could be easily coded in a macro, but without knowing the layout of the sheet and what you want to transfer and where, it would be hard to provide anything specific. Sub TransData() dim i as long, mysource as variant, mydest as variant dim myclear as variant mysource = Array("A1","B9","C11","G13") myDest = Array("M6","R13","Z26","B4") for i = lbound(mysource) to ubound(mysource) Range(mydest(i)).Value = Range(mysource(i)).Value Range(mysource(i)).ClearContents Next ' option - clear manual inputs myclear = Array(A3,B5:B30,R11,M31) for i = lbound(myclear) to ubound(myclear) Range(myclear(i)).ClearContents Next ' or ' Range("A3,B5:B30,R11,M31").ClearContents end Sub so fill in the appropriate cells in each array. mysource and mydest must have the same number of elements. -- Regards, Tom Ogilvy "Jun" wrote in message ... I have an Excel file with three worksheets which makes cross references to each other to get certain values. It is basically a simple weekly finace sheet. I was wondering if there was an easy one click way of deleting the contents of only those cells which require manual input each week. I am also looking to transfer the current week's figures into last week's figures cell ready for use the following week. Any hints and tips would be gratefully received. Many thanks. . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing cell contents automatically
if the button is from the control toolbox toolbar, then you call the macro
from the Click event of the commandbutton. If it is from the forms toolbar, you can assign the macro to the button. When you place the button on the sheet, you should be prompted to assign a macro. -- Regards, Tom Ogilvy Jun wrote in message ... Thank you for your advice. I will try it out. Is there a way of creating a button on the worksheet which can then execute the definition I create for the cells I want the contents deleted? I have created a macro to do it but am not sure if that can then be attached to a button. Thanks again. Jun -----Original Message----- Not exactly one click, but in the with a single cell selected, do Edit=goto=special and select Constants and Numbers, then OK, then delete. If this results in too much being deleted, you can select a more appropriate range and do it. Assumes manual inputs are numbers. If text is included, then don't restrict the selection to numbers. I am sure the transfer could be easily coded in a macro, but without knowing the layout of the sheet and what you want to transfer and where, it would be hard to provide anything specific. Sub TransData() dim i as long, mysource as variant, mydest as variant dim myclear as variant mysource = Array("A1","B9","C11","G13") myDest = Array("M6","R13","Z26","B4") for i = lbound(mysource) to ubound(mysource) Range(mydest(i)).Value = Range(mysource(i)).Value Range(mysource(i)).ClearContents Next ' option - clear manual inputs myclear = Array(A3,B5:B30,R11,M31) for i = lbound(myclear) to ubound(myclear) Range(myclear(i)).ClearContents Next ' or ' Range("A3,B5:B30,R11,M31").ClearContents end Sub so fill in the appropriate cells in each array. mysource and mydest must have the same number of elements. -- Regards, Tom Ogilvy "Jun" wrote in message ... I have an Excel file with three worksheets which makes cross references to each other to get certain values. It is basically a simple weekly finace sheet. I was wondering if there was an easy one click way of deleting the contents of only those cells which require manual input each week. I am also looking to transfer the current week's figures into last week's figures cell ready for use the following week. Any hints and tips would be gratefully received. Many thanks. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Clearing Contents | Excel Discussion (Misc queries) | |||
Macro for clearing cell contents | Excel Discussion (Misc queries) | |||
Clearing Cell Contents / Worksheet_Change Event | Excel Worksheet Functions | |||
Selectively Clearing cell contents | Excel Worksheet Functions | |||
Clearing Contents of Cell | Excel Worksheet Functions |