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

[moike]

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

 

[Bearacade]

I am not clear is you are using VBA or just plain formulas..

If you are using formulas, just drag and fill, unless you have absolute
reference to your cells in the first row, It should work ok.

If you are using VBA, you would have to include the other rows into
your codes.

 

[Guest]

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

 

[moike]

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?

 

[Bearacade]

The simpliest way of doing it would be:

For x = 1 To 100
Cells(x, 1).Select
(Your Macro/Fuction here)
Next x

This would select A1, B1, C1 and so forth

HT

 

[moike]

thanks, i'll try it out