VB Code is working for row1; how to apply to row2?

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

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

Originally Posted by Bearacade

Without seeing your codes and exactly what you are doing, it hard to say what you need to do.

Most likely a loop would help you run the macro thru all the neccessary rows.

You might need to include examples to make it more clear

Sure,

Here is more info about what I am doing.

Structure of the spreadsheet: Column C has a dollar amount, Column D has a date, column F has the term of the contract in months, columns G through Q are fiscal quarters.

I need the macro to first verify if the amount in column C is > or < than 100k.

If less than 100k; the full value of column C will be entered in whichever fiscal quarter includes the date of 1 month beyond the date of column D.

If the amount in C is greater than 100k; the value of column C must be divided by the number of months in the contract which is reflected in column F. This new amount must be entered in columns G through Q (fiscal quarters) but it can't start until 1 month after the date in column D.

Anyway, I have working code to do this in the first row but my code contains the cell locations associated with the first row. I need the code to continue through all rows with the same steps using the values on that row.

Are there other ways to signify which cells I want to include in my code other than calling out the location (ie: A1,A2)? I guess I could use:

Range("A1").Select
ActiveCell.FormulaR1C1 = "=RC[+1]"

and continue with that format for the rest of the steps so that the only time I call out a specific location would be the "A1" at the beginning. That would require me to use the same code over and over in the macro but keep changing the A1 to A2,A3, etc.

Is there an easier way (maybe a loop) to tell the macro where to start and then to move to the next row once finished with all the steps I outlined?

thanks, i'll try it out