Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy ranges
Hi there,
I want to write a Macro that will: (i) Copy a range from Sheet1!B5:AK87 into Sheet2!B5:AK87; (ii) For each cell in the new range in sheet 2, for each cell that is neither blank nor text (i.e. for each cell that is a number), inflate it's value by 2.5% for two years (i.e. multiply each of these cells by 1.050625). how do I do this? Also, what would happen to the Macro if I inserted a row into the original range in Sheet1? Would the Macro miss out the last row when copying and inflating? If I name the original range, does this solve the problem? (It is likely that rows and/or columns will be inserted into the original range at some point in the future, so tailoring the Macro to suit this would really help me) Many Thanks, Gary. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy ranges
Dim cell As Range Worksheets("Sheet1").Range("B5:AK87").Copy Destination:= _ Worksheets("Sheet2").Range("B5:AK87") For Each cell In Worksheets("Sheet2").Range("B5:AK87") If IsNumeric(cell.Value) And cell.Value < 0 Then cell.Value = cell.Value * 1.025 End If Next cell As you supect, the last line will be missed as it will now be row 88. Naming it and suing tghe name will hellp, as long as you use that name in the code. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Gary Thomson" wrote in message ... Hi there, I want to write a Macro that will: (i) Copy a range from Sheet1!B5:AK87 into Sheet2!B5:AK87; (ii) For each cell in the new range in sheet 2, for each cell that is neither blank nor text (i.e. for each cell that is a number), inflate it's value by 2.5% for two years (i.e. multiply each of these cells by 1.050625). how do I do this? Also, what would happen to the Macro if I inserted a row into the original range in Sheet1? Would the Macro miss out the last row when copying and inflating? If I name the original range, does this solve the problem? (It is likely that rows and/or columns will be inserted into the original range at some point in the future, so tailoring the Macro to suit this would really help me) Many Thanks, Gary. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I copy big ranges of cells without drag or copy/paste? | Excel Discussion (Misc queries) | |||
how copy formula that contains ranges so ranges do not overlap | Excel Worksheet Functions | |||
Copy and SUM functions with different ranges | Excel Worksheet Functions | |||
Copy entire ranges | Excel Worksheet Functions | |||
Copy sets of ranges | Excel Programming |