• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Automatic function - Macro?

M

mactoolsix

New Member
I have a column of values (about 40 cells), which are the result of several formulas. I want to multiply each cell by 12, and record the absolute value in another column. I would like to have this happen with a single command, i.e. "Crtl Q" or maybe by just clicking on a certain cell. Do I use a macro to accomplish this, and can someone get me started on how do this.

Thanks in advance for your time & help.

Mike
 
Hi Mike ,

This is a straightforward use of the Copy & Paste Special Multiply commands ; why do you need a macro for this ?

Narayan
 
Narayan,

I'm putting together a spreadsheet for a client that is not familiar with excel commands - trying to make this simple for him to just click on a cell or Crtl command and have the spreadsheet perform the task of Copy, Multiply and then paste the result into another sheet automatically.

Mike
 
Hi Mike ,

In that case , how will the copy and paste ranges be specified ? What about the multiplier , will it always be 12 ?

Narayan
 
The range & multiplier always the same, and will always paste to same column on a different sheet (were just replacing the existing values with updated data).
 
Hi Mike ,

I have not made myself clear ; the macro will have to use the Copy method and the Paste method ; this requires either the copy range to be pre-selected , so that we can use Selection.Copy , or it needs to be specified within the macro using either absolute addressing ( e.g. C7:Z39 ) or a named range ( e.g. Copy_Range , which will need to be created in the workbook ) ; a similar situation applies to the paste range.

If you say that the copy and paste ranges are on different worksheets , then we will need to decide on how these will be specified.

Narayan
 
Narayan - As usual your solution works!!

I notice it asks to "select the cells where you wish to paste the copied range" Since this will be an ongoing sheet, they may want to copy the result to a different column each year. I tried changing the selection from $J$7 to $L$7, however it still copies to J7. Is there a way to make the cells copied to an option?

Thanks again,
Mike
 
Narayan - checked the revised sheet. I see where you edited the code for user selection range, however when I enter a selection for the destination, it still copies to J7.

I'll try doing a recorded macro, except not sure how I'm going to make a destination selection possible - I'll play with it.

Thanks!!
Mike
 
Hi Mike ,

I tried with the file I have uploaded , and it does what you wanted ; I tried two methods :

1. Using the mouse to select a cell

2. Entering a different address such as L7

In both cases , it worked correctly. Can you try once more ?

Narayan
 
Narayan,

Yep - I tried again but not allowing me to change the destination.
I just read about pausing a macro to allow a user input - "Make sure ScreenUpdating is turned on. Otherwise, you won't be able to select a cell." (I'm using excel 2010)
I don't see a line to turn on ScreenUpdating in the code?
 
Hi Mike ,

This is baffling ; it is working correctly at my end !

Can you do the following ?

1. Go into your VBE , and put a breakpoint at the following line in the macro :

If dest_range Is Nothing Then

To do this , place the cursor at the line , and press the F9 key.

2. Go back to the worksheet , and press CTRL t to run the macro ; after you have entered the desired destination start cell , and pressed OK , code execution will halt.

In the Immediate window , type in the following and press the ENTER key :

?dest_range.Address

Excel should display the address of the cell which you had selected.

If it does not , then in some manner your system is different from mine , because on mine , when I follow the above steps , the displayed address matches the one I selected.

Narayan
 
Narayan,

I think I had too many "sample" wrkshts open and confused your most recent modification sample 2-1. I deleted all of them and downloaded a fresh sample 2-1 - WORKS GREAT!!!

I think I'll be able to make the necessary modifications to work with my spreadsheets & ranges.

Thanks again for your time.
Mike
 
Narayan,
Okay - one more thing. I have spent an hour trying to figure out how to transfer the macro you created to the workbooks I need to use it in. If the "sample" workbook is open, it uses that data. If it is not open it asks for the range of data, then creates a pivot table.

Sorry for all the questions,
Mike
 
Hi Mike ,

Can you explain in a little more detail ?

Suppose you have two workbooks A and B ; which workbook will have the macro in it ?

You say that if the 'sample' workbook is not open , it asks for the range of data ; so will it use the indicated range in the workbook which has the macro ?

After that , you say it will create a pivot table ; what is this ?

It would be nice if you could upload two files , and indicate which of them should have the macro in it.

I will test the two scenarios , and see how it can work the way you want it to.

Narayan
 
You must log in or register to reply here.
Back
Top