G

#### Guest

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

G

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

Ad

G

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.

G

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

G

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

Ad

R

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

**Want to reply to this thread or ask your own question?**

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.