Code help required
 

I have a worksheet that displays cash flows in 60 columns of a ro
Rrepesenting 60 months).

Depending on the input of another cell "X1" (containing possible
range 0 to 12) I want all 60 cashflows to move to the right by th
value (from 0 to 12, 0 representing stay as is).

i.e. if value x1 = 6 value in cell "A1" is now in "A7", "A2 is in "A8
etc

Is there an easy way to code this??

Appreciate any ideas,

Cheers

Pete

--
peter.thompso
-----------------------------------------------------------------------
peter.thompson's Profile: http://www.excelforum.com/member.php...fo&userid=2968
View this thread: http://www.excelforum.com/showthread.php?threadid=49927

Code help required
 

Hi Peter,

Try this macro, where rownum is the row number in which you want to shift
the values, distance is the value in X1):
Sub shiftrow(rownum As Long, distance As Long)
For shift = 60 To distance + 1 Step by - 1
Cells(rownum, shift).Value = Cells(rownum, shift - distance).Value
Next shift
End Sub


I suppose that you mean in

i.e. if value x1 = 6 value in cell "A1" is now in "A7", "A2 is in "A8"

"A7" should be "G1" that is Cells(1,1) shifts to the right to Cells(1,7)

Regards,
Stefi

Code help required
 

Sub shiftrow(rownum As Long, distance As Long)
Cells(rownum, 1).Resize(, 60).Cut
Cells(rownum, 1 + distance).Select
ActiveSheet.Paste
End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Stefi" wrote in message
...
Hi Peter,

Try this macro, where rownum is the row number in which you want to shift
the values, distance is the value in X1):
Sub shiftrow(rownum As Long, distance As Long)
For shift = 60 To distance + 1 Step by - 1
Cells(rownum, shift).Value = Cells(rownum, shift - distance).Value
Next shift
End Sub


I suppose that you mean in

i.e. if value x1 = 6 value in cell "A1" is now in "A7", "A2 is in "A8"

"A7" should be "G1" that is Cells(1,1) shifts to the right to Cells(1,7)

Regards,
Stefi

Code help required
 

Hi Peter

Your posting is a little ambiguous.
You say you want to move X1 columns to the right, but your example shows
X1 rows down.

A possible non VBA solution might be named ranges.
Suppose we have "alldata" defined as $A$2:$CG200 (this will allow up to
24 monthly shifts to the right).
Suppose your base cash flow is 60 columns wide by 100 rows deep.
Then define "cashflow" as
=INDEX(alldata,1,1+X1):INDEX(alldata,100,60+X1) if you want to shift
the data to the right
or
=INDEX(alldata,1+X1,1):INDEX(alldata,100+X1,60) if you want to shift it
down the page

--
Regards

Roger Govier


"peter.thompson"
<peter.thompson.21ct2y_1136787301.3137@excelforu m-nospam.com wrote in
message
news:peter.thompson.21ct2y_1136787301.3137@excelfo rum-nospam.com...

I have a worksheet that displays cash flows in 60 columns of a row
Rrepesenting 60 months).

Depending on the input of another cell "X1" (containing possible a
range 0 to 12) I want all 60 cashflows to move to the right by the
value (from 0 to 12, 0 representing stay as is).

i.e. if value x1 = 6 value in cell "A1" is now in "A7", "A2 is in "A8"
etc

Is there an easy way to code this??

Appreciate any ideas,

Cheers

Peter


--
peter.thompson
------------------------------------------------------------------------
peter.thompson's Profile:
http://www.excelforum.com/member.php...o&userid=29686
View this thread:
http://www.excelforum.com/showthread...hreadid=499279

Code help required
 

Thanks everyone

Cheers

Peter


--
peter.thompson
------------------------------------------------------------------------
peter.thompson's Profile: http://www.excelforum.com/member.php...o&userid=29686
View this thread: http://www.excelforum.com/showthread...hreadid=499279