Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shift constant values in a range left or right by n columns
Hi, Excel folks -
Given a range containing some cells with constant values and some cells with formulas, what is the most efficient way to shift only the constant values left or right by a specified number of columns, leaving formulas and all formatting in source and target cells unchanged? I'd also want to clear the values in the cells from which the data has been moved. What this is all about is a worksheet that contains columns representing months of data, with the rows representing line items or various subtotals. I want to advance or retreat the date window by a user-specified number of months. I've worked out how to do this by looping through the rows and columns of the range, but (a) the code is slow, taking about several seconds to execute, and (b) it seems to me this is not the "Excel" way to do this. I feel like there ought to be a set of simple range operations to accomplish it. Can you advise me, please? -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shift constant values in a range left or right by n columns
Hi,
For the first part of your question, this may help. I have a budget spread sheet and this is how I handle it: I am using a named range, but you can also use a hardcoded range as well; then I use the offset to copy what I need, then I use paste special (PasteValues) If you use PasteValues, then the values will be pasted indentically to the copied values 'Copy Column C bills Sheets(1).Activate Sh1bW.Offset(0, 2).Copy 'Paste bills one column to the right of named range Sh2bw Sheets(2).Activate Sh2bw.Offset(0, 1).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone For the clear part, it is your source range .ClearContents Also, I actually have a macro callled Shift Left, see if this helps you as well Between the two, you should be able to piece something together. If not, I will try to help you more ML Sub ShiftLeft() Application.ScreenUpdating = False Dim i As Integer On Error Resume Next 'If the cell in column C - the cell in Column B is <= 0, Column B (cell) is null For i = 6 To 106 If (Range("B" & i).Value - Range("C" & i).Value) <= 0 Then Range("B" & i).Value = "" Else 'Deduct the amount paid that week (Column C) from The Amt.Due (Column B) Range("B" & i).Value = Range("B" & i).Value - Range("C" & i).Value 'Change cell format in Column B back to accounting Range("B" & i).NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)" End If Next i 'Shift weeks to the left Range("C3:C106").Select Selection.ClearContents Range("D3:D106").Select Selection.Copy Range("C3").Select ActiveSheet.Paste Range("E3:E106").Select Selection.Copy Range("D3").Select ActiveSheet.Paste Range("F3:F106").Select Selection.Copy Range("E3").Select ActiveSheet.Paste Range("G3:G106").Select Selection.Copy Range("F3").Select ActiveSheet.Paste Range("G3:G106").Select Selection.ClearContents 'Values for column "G3" through "G5" 'Copy The formula in "F5" to "G5" Range("F5").Select Selection.Copy Range("G5").Select ActiveSheet.Paste 'The net pay in "G4" = The (Bi-Weekly) next pay in E4 Range("G4").Value = Range("E4") Dim IntervalType As String Dim Number As Integer Dim FirstDate As Date IntervalType = "d" '"d" specifies days as interval. Number = 7 FirstDate = Range("F3").Value 'The date in "G3" = The date in "F3" + 7 days Range("G3").Value = DateAdd(IntervalType, Number, FirstDate) Range("A2").Select Selection.ClearContents End Sub "Dirk Goldgar" wrote: Hi, Excel folks - Given a range containing some cells with constant values and some cells with formulas, what is the most efficient way to shift only the constant values left or right by a specified number of columns, leaving formulas and all formatting in source and target cells unchanged? I'd also want to clear the values in the cells from which the data has been moved. What this is all about is a worksheet that contains columns representing months of data, with the rows representing line items or various subtotals. I want to advance or retreat the date window by a user-specified number of months. I've worked out how to do this by looping through the rows and columns of the range, but (a) the code is slow, taking about several seconds to execute, and (b) it seems to me this is not the "Excel" way to do this. I feel like there ought to be a set of simple range operations to accomplish it. Can you advise me, please? -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shift constant values in a range left or right by n columns
"ML0940" wrote in message
... Hi, For the first part of your question, this may help. I have a budget spread sheet and this is how I handle it: I am using a named range, but you can also use a hardcoded range as well; then I use the offset to copy what I need, then I use paste special (PasteValues) If you use PasteValues, then the values will be pasted indentically to the copied values 'Copy Column C bills Sheets(1).Activate Sh1bW.Offset(0, 2).Copy 'Paste bills one column to the right of named range Sh2bw Sheets(2).Activate Sh2bw.Offset(0, 1).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone For the clear part, it is your source range .ClearContents Also, I actually have a macro callled Shift Left, see if this helps you as well Between the two, you should be able to piece something together. Thanks, this is the sort of thing I was looking for. Does this work, especially the Offset, if the range is discontinuous? Given that I want to ignore rows that contain subtotalling formulas, I thought I might use SpecialCells to get a range containing only those cells that have constant values -- but that would be a discontinuous range. By the way, I noticed this in your ShiftLeft macro code: Range("B" & i).Value = Range("B" & i).Value - Range("C" & i).Value 'Change cell format in Column B back to accounting Range("B" & i).NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)" In testing my own looping code, I found that setting a cell's Value property seemed to reset its format, and so I used code to save the format and then restore the format after setting the value. I wasn't sure that I ought to have to do that, and thought maybe I was missing something. But your code suggests that losing the formatting is just something that happens, and you have to deal with it. Is that right? Thanks for your assistance, -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shift cells to the left | Excel Programming | |||
Searching a date range to output values according to a constant. | Excel Discussion (Misc queries) | |||
Searching a date range to output values according to a constant. | Excel Discussion (Misc queries) | |||
Assign a 2D array of constant values to a range | Excel Programming | |||
Shift data to left | Excel Programming |