Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 47
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Shift cells to the left Steve D.[_2_] Excel Programming 4 July 9th 07 01:26 AM
Searching a date range to output values according to a constant. Geebz Excel Discussion (Misc queries) 2 December 1st 06 10:34 AM
Searching a date range to output values according to a constant. daddylonglegs Excel Discussion (Misc queries) 0 November 30th 06 09:23 PM
Assign a 2D array of constant values to a range dodgo Excel Programming 4 October 19th 06 04:46 AM
Shift data to left Steph[_3_] Excel Programming 4 April 13th 05 03:58 AM


All times are GMT +1. The time now is 04:59 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"