Solved

Simple Move Macro

Posted on 2013-06-22
7
145 Views
Last Modified: 2013-06-28
EE Pros,  

I need a macro that will reset values in particular cells (C7:C12) with the values in another set of sells (E7:E12) when the Reset Button is pushed.  I would use a formula but the values in the C cells may change until reset is fired.

Also, is there a way to list a set of ranges in one line of code?   I usually write numerous lines when I have cells in a group (not using Naming conventions) and it would be helpful to know how to list a string of cell references in one line of code.

Thank you in advance,

B.
Simple-move-with-Macro.xlsm
0
Comment
Question by:Bright01
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 39267989
Hi,

pls try this

    Range(Range("E7"), Range("E35000").End(xlUp)).Copy
    Range("C7").PasteSpecial Paste:=xlPasteValue

Open in new window


for the ranges

you can use if theere is a continuity
Range("C7:E12") or
Range(Range("E7"), Range("C12"))

or you can use unions see ref http://www.vbaexpress.com/kb/getarticle.php?kb_id=354

Regards
0
 

Author Comment

by:Bright01
ID: 39268242
Thaks Rgonzo.

I get a debug error.  Can you put the code in the WS and try it?  It's "out of range"........

TY,

B.
0
 
LVL 10

Assisted Solution

by:broro183
broro183 earned 250 total points
ID: 39268850
hi Bright01,

Have you tried recording a macro?
To do this, open your file, press [alt + t + m + r], perform your desired actions & then look at the generated code. This approach will help you learn & the recorded code will often give you a good starting point.


When I recorded a quick macro the following code was recorded:

Range("E7:E12").Select
    Selection.Copy
    Range("C7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

Open in new window


This can be shortened (through experience) to:
Range("E7:E12").Copy
    Range("C7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

Open in new window

Or alternatively, my preferred approach (preferred because it doesn't rely on the clipboard) is:
Range("C7:12").value = Range("E7:E12").value

Open in new window

To make this more robust so that it explicitly states which sheet (& in which file) is modified, I would write it as:
    With ThisWorkbook.Worksheets("Assumptions - Benefits & Costs")
        .Range("C7:12").Value = .Range("E7:E12").Value
    End With

Open in new window


Regarding your second question. I used the same approach of recording a macro where I selected multiple ranges using the mouse & then changed the value in all of the selected cells in one go. This is the recorded code:

    Range("E14,F5,C5,C14,B17:C20").Select
    Range("B17").Activate
    Selection.FormulaR1C1 = "23"

Open in new window


With the understanding that it is rarely necessary to "select" or "activate" cells, you could shorten the above code to become:
    Range("E14,F5,C5,C14,B17:C20").FormulaR1C1 = "23"
'or because in this example, the change is just entering a value, it could be written as
    Range("E14,F5,C5,C14,B17:C20").value = "23"

Open in new window


This shows that multi-area ranges can be written in a single line of code within the appostrophes that are inside the pair of brackets (parenthesis?) relating to the range.


Note, that this approach (or using Union) may become slow when there is a very large number of individual ranges involved.

hth
Rob
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 14

Accepted Solution

by:
Faustulus earned 250 total points
ID: 39269163
The single line of code you prefer would be this one:
Sub RefreshCells()
    Range("E7:E12").Copy Destination:=Range("C7")
End Sub

Open in new window

It will copy everything, and if you have any formulas in E7:12 they will be copied, too. Make sure that they are formatted absolute (with $ signs) for columns.

If you would rather copy the values the formulas produce but not the formulas themselves you need more than one line:-
Sub RefreshCells()
    Range("E7:E12").Copy
    Range("C7").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
End Sub

Open in new window

I suggest that you use a keyboard shortcut to call either macro.
From the Developer tab select Macros -> Select 'RefreshCells' => Options and enter the shortcut code you like to use. Whether you use this option or not, make sure that the macro is in a standard code module (by default 'Module1').
0
 

Author Closing Comment

by:Bright01
ID: 39282549
Broro and Faust,

Thank you so much!  Broro, I'm now working to learn how to record macros...thank you for the lessons.  Very helpful.  Faust, I used your code

Sub RefreshCells()
    Range("E7:E12").Copy Destination:=Range("C7")
End Sub

To work my problem in my workbook.

Thank you both for your time and patience on this one.

All the best,

B.
0
 
LVL 10

Expert Comment

by:broro183
ID: 39285915
Thanks for the points B :-)

Faustulus,
Trnasferring (rather than "copying") the values can actually be done in a single line as I showed in my previous post. I also showed a more explicit 3 line version which incorporates a With statement. Here's the one liner:

Range("C7:12").value = Range("E7:E12").value

Open in new window


Rob
0
 
LVL 14

Expert Comment

by:Faustulus
ID: 39286277
Rob,
Yes, of course. I seem to have become stuck in a groove there. :-)
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now