Self-Perpetuating Formula

Nice, should work well.

While I've had experience coding in various manners, I never officially learned VBA.

What if I wanted to only take from row A32 onward?

Is it just : Me.[a32:a] ?

In addition, what if I have several different formulas, in different columns, awaiting this data. Would I just create a new module for each formula or could I just add more lines as follows:
...

For Each cel In rng.Cells
            Cells(cel.Row, "D").Formula = "=A" & cel.Row & "/B" & cel.Row
            Cells(cel.Row, "E").Formula = "=A" & cel.Row & "/B" & cel.Row
            Cells(cel.Row, "F").Formula = "=A" & cel.Row & "/B" & cel.Row
        Next
...

And how does it know what "formula" to input?

Anyway...
If I'm far off then maybe just give me a good link that I can use to study up on VBA? No need to spend too much time on this.

Thanks, regardless.

PGRBryant said:
>>While I've had experience coding in various manners, I never officially learned VBA.

Then you've come to the right place :)

>>What if I wanted to only take from row A32 onward?
>>
>>Is it just : Me.[a32:a] ?

Close.  Me.[a32:a65536]

At least, that covers you through Excel 2003.

>>In addition, what if I have several different formulas, in different columns, awaiting this data. Would I just
>>create a new module for each formula or could I just add more lines as follows:
>>...
>>
>>For Each cel In rng.Cells
>>            Cells(cel.Row, "D").Formula = "=A" & cel.Row & "/B" & cel.Row
>>            Cells(cel.Row, "E").Formula = "=A" & cel.Row & "/B" & cel.Row
>>            Cells(cel.Row, "F").Formula = "=A" & cel.Row & "/B" & cel.Row
>>        Next

The above would work just fine.

>>And how does it know what "formula" to input?

Well, you would have to specify that in each line :)

>>Anyway...
>>If I'm far off then maybe just give me a good link that I can use to study up on VBA? No need to spend too much time on this.

1) Study the work of the top Experts here
2) Use the macro recorder to record your own actions
3) Pick up *anything* written by John Walkenbach :)

Ah, brillant.

Okay so, when I'm specifying it in each line, do I literally write out the entire formula or is it some kind of reference?

i.e.
Cells(cel.Row, "D").Formula = "=A" & cel.Row & "/B" & cel.Row

changes to (which feels wrong):
Cells(cel.Row, "D").if((A>10)*(A<11),"dance","cry") = "=A" & cel.Row & "/B" & cel.Row

Last one before you get your awesomely deserved solution points. ;)

A very handy manual technique for copying down a formula is the following:
1) Put the formula in the first cell to the right of your data
2) Select the cell with the formula
3) Double-click the little square at the bottom right corner of the selection marquee. This will copy the formula down until the first break (blank) in your data.

Brad

PGRBryant said:
>>changes to (which feels wrong):
>>Cells(cel.Row, "D").if((A>10)*(A<11),"dance","cry") = "=A" & cel.Row & "/B" & cel.Row

You certainly would not add it like that :)

From the line above, I am not able to figure out what you want to do.  Please explain, and I am sure I will be
able to help you (unless Brad catches it first, of course!).

Essentially, I'm doing a bunch of statistical analysis on data that is being placed into the excel sheet in 4 columns. A person gets this data and then copy+pastes it into the first four columns of the excel sheet. Once this data is placed, then there are several subsequent columns that do things like: throw out outliers, change the frequency data to MHz, analyze special frequencies differently, etc.

This data--it's all coming in the same format, from the same machine--can vary widely: from 400 to 10000+ points.

What I'd like to have happen is the excel sheet to "respond" to the data. That is, once it's placed the other columns that have varying formulas would grow or shrink at the same amount.

Part of the reason I'd prefer to have it set up this way, is because, otherwise, scrolling can be unwieldy when the data is small and I've got 25000 lines of formula's referencing nothing (not a very elegant solution).

By far the easiest formula is the MHz conversation. This just says,

=A1/1000000

Others, however, reference several columns (or specific values) at once in order to get the job done.

Anyway, you asked for more details, and there you have it. :)

Honestly, I'm most impressed with the attitudes and intelligence of the members of this community. Well done you guys, and thanks much!
I'll definitely have to "pay it forward," when I can.

PGRBryant,
Thanks for the assist!  Here's a variant on Brad's code.  Simply select the formulas which you want to fill down and run the code (cells do not need to be contiguous).  It will fill down until it reaches the last row containing data in the column you specify.

Kyle

Sub FormulaFiller2()
Dim iCol As Long
Dim rng As Range
iCol = 1 'column which contains the data at the end of which you wish to stop the formulas
For Each rng In Selection.Areas
    Range(rng, Cells(Cells(Rows.Count, iCol).End(xlUp).Row, rng.Column)).FillDown
Next rng
End Sub

Select allOpen in new window