copy a value across a row but into every nth cell

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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 $.

 

[RagDyeR]

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!
=====================================================

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