Code help required

[peter.thompson]

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

 

[Guest]

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

 

[Bob Phillips]

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)

 

[Roger Govier]

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"
message
news[email protected]...

 

[peter.thompson]

Thanks everyone

Cheers

Peter