Not being clairvoyant, I am somewhat strapped by the minimal information
provided.

However, assuming your code is a User Defined Function used in worksheet and
you want to generalize to work relative to its location, you can use

Public Function CalcSpread(somearg)
Dim rng as Range, rngDate as Range, rngDollar as Range
Dim rate as Double, Answer as Double
set rng = Application.Caller
with rng.Parent
set rngDate = .Range(.Cells(rng.row,3),.cells(rng.row,14))
set rngDollar = .Cells(rng.row,15)
End With
rate = rng.Dollar/ rng.Date.count
' blah blah

CalcSpread = Answer
End Function

--
Regards,
Tom Ogilvy


"moike" wrote:


First off: This forum has been VERY helpful for me and I have learned
way more than I thought I ever would about Excel and VB. I have
automated a few spreadsheets we work with at my current job with a
little help from the search function here.

Now onto my current mess. I spent most of yesterday evening helping
someone automate a spreadsheet with MANY different IF:Then scenarios
which are triggered by dates, dollar amounts, and the period of
contracts. The spreadsheet allocates dollar amounts across our fiscal
quarters beginning with the month following the provided date. The
amount allocated is provided in another column and divided by the
length of a contract which is shown in a separate column.

I spent several hours (and I'm sure I used way more lines of code than
necessary) and have working macros for the first row of the
spreadsheet. There are 100 more rows below this one where I need the
same macro to occur. There has to be an easier way than copying and
pasting the current formulas and changing all the cell values mentioned
to match the next row. Any suggestions???

Appreciate any help you can provide...


--
moike
------------------------------------------------------------------------
moike's Profile: http://www.excelforum.com/member.php...o&userid=37668
View this thread: http://www.excelforum.com/showthread...hreadid=572754