Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another VB Code Required | Excel Discussion (Misc queries) | |||
VB Code Required | Excel Discussion (Misc queries) | |||
Macro Code Required - Pls Help | Excel Discussion (Misc queries) | |||
help required in completing the code | Excel Discussion (Misc queries) | |||
Code required please | Excel Programming |