Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy a value across a row but into every nth cell
Say I have a table that has a variable (say$50) in cell 1 and is 12 months
wide (i.e. 13 cells across) I want to copy the variable into every nth cell (where n is also a variable) In this way I can say that certain expenses occur every n months. Any suggestions greatly appreciated. Mike |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy a value across a row but into every nth cell
The best way is to setup some shortcut keys to move around the worksheet.
Sub copyright() ActiveCell.Copy ActiveCell.Offset(rowoffset:=0,columnofffset:= 12).Activate End Sub The above macro copies the activecell and then moves right 12 cells. You can assign the macro a shortcut key by going on worksheet to Tools Menu Macro - Options. Then enter a letter in the shortcut box. The macro above could be modified to also do the paste. You should also learn other standard shortcut keys available by searching in the Worksheet help for "Shortcut". For example Cntl right arrow gets you to the first column. "MickinOz" wrote: Say I have a table that has a variable (say$50) in cell 1 and is 12 months wide (i.e. 13 cells across) I want to copy the variable into every nth cell (where n is also a variable) In this way I can say that certain expenses occur every n months. Any suggestions greatly appreciated. Mike |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy a value across a row but into every nth cell
Thanks Joel,
That is useful but not quite what I want (being lazy I don't want to fill in 100's of cells manually). I came up with a kludge: F G H I J Cost Freq Start End Incr 1,100 A 1 29 5% Row 2 cell K2 is an integer representing the month (1,2,3, etc.) Freq can be A (annual) Q (quarterly) or M (monthly, max 36). The value in J is an allowance for cost increase. And the formula in K3 and each cell rightwards from there is: =IF(K$2$I4,0,IF(AND($G4="a",OR(K$2=$H4,K$2=$H4+12 ,K$2=$H4+24)),$F4*(1+($J4/12))^K$2,IF(AND($G4="q",OR(K$2=$H4,K$2=$H4+3,K$2=$ H4+6,K$2=$H4+9,K$2=$H4+12,K$2=$H4+15,K$2=$H4+18,K$ 2=$H4+21,K$2=$H4+24,K$2=$H4+27,K$2=$H4+30,K$2=$H4+ 33,K$2=$H4+36)),$F4*(1+($J4/12))^K$2,IF(AND($G4="m",K$2=$H4),$F4*(1+($J4/12))^K$2,0)))) Messy, but it works. If you can reproduce it maybe you can tell me how to simplify it. Thanks again Mike Joel" wrote: The best way is to setup some shortcut keys to move around the worksheet. Sub copyright() ActiveCell.Copy ActiveCell.Offset(rowoffset:=0,columnofffset:= 12).Activate End Sub The above macro copies the activecell and then moves right 12 cells. You can assign the macro a shortcut key by going on worksheet to Tools Menu Macro - Options. Then enter a letter in the shortcut box. The macro above could be modified to also do the paste. You should also learn other standard shortcut keys available by searching in the Worksheet help for "Shortcut". For example Cntl right arrow gets you to the first column. "MickinOz" wrote: Say I have a table that has a variable (say$50) in cell 1 and is 12 months wide (i.e. 13 cells across) I want to copy the variable into every nth cell (where n is also a variable) In this way I can say that certain expenses occur every n months. Any suggestions greatly appreciated. Mike |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy a value across a row but into every nth cell
There arre tow suggestions
1) I HATE complicated worksheet functions. I can't debug them. I rather write my own UDF so I can step through the code a get them to work. Using the Mod function can really simplifier your code. Here is my version of your function in UDF. function mycalc(MyMonth, Cost,Freq,Start,End,Incr) if month end then mycalc = 0 else select case freq case "A" if (MyMonth mod 12 = 0) mycalc = Cost*(1+(Inc/12))^Month else mycalc = "" end if case "q" if (MyMonth mod 3 = 0) mycalc = Cost*(1+(Inc/12))^Month else mycalc = "" end if case "m" if (MyMonth Start) mycalc = Cost*(1+(Inc/12))^Month else mycalc = "" end if end if end if 2) If columns F the K repeat in as you go across your worksheet then why to you have $ like the line below from $G4="a",OR(K$2=$H4,K$2=$H4+12,K$2=$H4+24) to G4="a",OR(K$2=H4,K$2=H4+12,K$2=H4+24) If you copy the formula to the right like you asked in you original posting the formula will not copy properly. Remove the $. "MickinOz" wrote: Thanks Joel, That is useful but not quite what I want (being lazy I don't want to fill in 100's of cells manually). I came up with a kludge: F G H I J Cost Freq Start End Incr 1,100 A 1 29 5% Row 2 cell K2 is an integer representing the month (1,2,3, etc.) Freq can be A (annual) Q (quarterly) or M (monthly, max 36). The value in J is an allowance for cost increase. And the formula in K3 and each cell rightwards from there is: =IF(K$2$I4,0,IF(AND($G4="a",OR(K$2=$H4,K$2=$H4+12 ,K$2=$H4+24)),$F4*(1+($J4/12))^K$2,IF(AND($G4="q",OR(K$2=$H4,K$2=$H4+3,K$2=$ H4+6,K$2=$H4+9,K$2=$H4+12,K$2=$H4+15,K$2=$H4+18,K$ 2=$H4+21,K$2=$H4+24,K$2=$H4+27,K$2=$H4+30,K$2=$H4+ 33,K$2=$H4+36)),$F4*(1+($J4/12))^K$2,IF(AND($G4="m",K$2=$H4),$F4*(1+($J4/12))^K$2,0)))) Messy, but it works. If you can reproduce it maybe you can tell me how to simplify it. Thanks again Mike Joel" wrote: The best way is to setup some shortcut keys to move around the worksheet. Sub copyright() ActiveCell.Copy ActiveCell.Offset(rowoffset:=0,columnofffset:= 12).Activate End Sub The above macro copies the activecell and then moves right 12 cells. You can assign the macro a shortcut key by going on worksheet to Tools Menu Macro - Options. Then enter a letter in the shortcut box. The macro above could be modified to also do the paste. You should also learn other standard shortcut keys available by searching in the Worksheet help for "Shortcut". For example Cntl right arrow gets you to the first column. "MickinOz" wrote: Say I have a table that has a variable (say$50) in cell 1 and is 12 months wide (i.e. 13 cells across) I want to copy the variable into every nth cell (where n is also a variable) In this way I can say that certain expenses occur every n months. Any suggestions greatly appreciated. Mike |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy a value across a row but into every nth cell
Say your variable (50) is in A10, and you want to copy it across to every
nth cell, from B10 to M10. Your n value is entered in A9. Try this formula in B10, and copy across to M10: =(MOD(COLUMNS($A:A),$A$9)=0)*$A$10 -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "MickinOz" wrote in message ... Thanks Joel, That is useful but not quite what I want (being lazy I don't want to fill in 100's of cells manually). I came up with a kludge: F G H I J Cost Freq Start End Incr 1,100 A 1 29 5% Row 2 cell K2 is an integer representing the month (1,2,3, etc.) Freq can be A (annual) Q (quarterly) or M (monthly, max 36). The value in J is an allowance for cost increase. And the formula in K3 and each cell rightwards from there is: =IF(K$2$I4,0,IF(AND($G4="a",OR(K$2=$H4,K$2=$H4+12 ,K$2=$H4+24)),$F4*(1+($J4/12))^K$2,IF(AND($G4="q",OR(K$2=$H4,K$2=$H4+3,K$2=$ H4+6,K$2=$H4+9,K$2=$H4+12,K$2=$H4+15,K$2=$H4+18,K$ 2=$H4+21,K$2=$H4+24,K$2=$H4+27,K$2=$H4+30,K$2=$H4+ 33,K$2=$H4+36)),$F4*(1+($J4/12))^K$2,IF(AND($G4="m",K$2=$H4),$F4*(1+($J4/12))^K$2,0)))) Messy, but it works. If you can reproduce it maybe you can tell me how to simplify it. Thanks again Mike Joel" wrote: The best way is to setup some shortcut keys to move around the worksheet. Sub copyright() ActiveCell.Copy ActiveCell.Offset(rowoffset:=0,columnofffset:= 12).Activate End Sub The above macro copies the activecell and then moves right 12 cells. You can assign the macro a shortcut key by going on worksheet to Tools Menu Macro - Options. Then enter a letter in the shortcut box. The macro above could be modified to also do the paste. You should also learn other standard shortcut keys available by searching in the Worksheet help for "Shortcut". For example Cntl right arrow gets you to the first column. "MickinOz" wrote: Say I have a table that has a variable (say$50) in cell 1 and is 12 months wide (i.e. 13 cells across) I want to copy the variable into every nth cell (where n is also a variable) In this way I can say that certain expenses occur every n months. Any suggestions greatly appreciated. Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to copy the first word or two words from a cell containing a complete sentence to another cell | Excel Discussion (Misc queries) | |||
I copy a formula and the results copy from the original cell | Excel Discussion (Misc queries) | |||
Copy data in one cell to blank cell immediately below, repeat | Excel Worksheet Functions | |||
Select cell, Copy it, Paste it, Return to Previous cell | Excel Discussion (Misc queries) | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions |