Code help required

[peter.thompson]

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

[Stefi]

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)

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

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

[peter.thompson]

Thanks everyone

Cheers

Peter