I have a dream...(about formula help)

[Lythande]

...and in this dream, behold: a spreadsheet with one row I need to work on.
In a cell I'll call M7 there is a dollar amount is not to the dollar ($327.56) so I manually round up then I run a macro that does the following formulas:

01. Cell O7 = N7*M7
02. Cell P7 = ROUND(O7*.06,2)
03. Cell Q7 = P7+O7

This is well and good--I am pleased.

But, alas, the dream turns into a nightmare when in other spreadsheets I have 1300 rows and many cells in column M that need rounding, and I must manually type in the above formulas into the corresponding cells to the right of whatever M cell is off because the macro/formulas are cell specific--and despair envelopes me in its dark mirth.

But lo, light from this message board beckons me...ask and ye shall receive, those who thirst for knowledge...come to us. I heed the summons.

So, that being said (and me in a dramatic mood) is it possible to have a formula/macro that will do the above three things to the O/P/Q cells to the right of a M cell that I adjust? My formulas only work on row seven darn it.

[Bryan Hessey]

...and in this dream, behold: a spreadsheet with one row I need to work on.
In a cell I'll call M7 there is a dollar amount is not to the dollar ($327.56) so I manually round up then I run a macro that does the following formulas:

01. Cell O7 = N7*M7
02. Cell P7 = ROUND(O7*.06,2)
03. Cell Q7 = P7+O7

This is well and good--I am pleased.

But, alas, the dream turns into a nightmare when in other spreadsheets I have 1300 rows and many cells in column M that need rounding, and I must manually type in the above formulas into the corresponding cells to the right of whatever M cell is off because the macro/formulas are cell specific--and despair envelopes me in its dark mirth.

But lo, light from this message board beckons me...ask and ye shall receive, those who thirst for knowledge...come to us. I heed the summons.

So, that being said (and me in a dramatic mood) is it possible to have a formula/macro that will do the above three things to the O/P/Q cells to the right of a M cell that I adjust? My formulas only work on row seven darn it.

Hi,

These formulae look like they would work on each row, did you Formula Fill them, and to what result?

see http://www.mvps.org/dmcritchie/excel/fillhand.htm

hth
---

[Lythande]

Hello Bryan,

Actually, I didn't formula fill them...and that link you offered looks pretty good--I'll sit down with a cup of joe tomorrow morning and play with it. I like
the macro solution that goes like this...

Filldown as a Macro solution (#filld)
The following will fill down as far down as the column to the left has content, as opposed to contiguous content. It will continue to fill down even if there are gaps in data to left but stops when there in no more data in the column to left.

Sub filld_to_last_at_left()
'Fill down to lastrow based on cell to left, D.McRitchie 2005-06-14 programming
' http://www.mvps.org/dmcritchie/excel/fillhand.htm#filld
If IsEmpty(ActiveCell) Then Exit Sub
Range(ActiveCell, Cells(Rows.Count, _
ActiveCell.Column - 1).End(xlUp).Offset(0, 1)).FillDown
End Sub


So I could apply my macro somewhere, then have a macro that
copies and pastes it where I need to and somewhere in the maro
I pasted in the above formula? That would be sweet if that's all
there is to it.

[Bryan Hessey]

Hello Bryan,

Actually, I didn't formula fill them...and that link you offered looks pretty good--I'll sit down with a cup of joe tomorrow morning and play with it. I like
the macro solution that goes like this...

Filldown as a Macro solution (#filld)
The following will fill down as far down as the column to the left has content, as opposed to contiguous content. It will continue to fill down even if there are gaps in data to left but stops when there in no more data in the column to left.

Sub filld_to_last_at_left()
'Fill down to lastrow based on cell to left, D.McRitchie 2005-06-14 programming
' http://www.mvps.org/dmcritchie/excel/fillhand.htm#filld
If IsEmpty(ActiveCell) Then Exit Sub
Range(ActiveCell, Cells(Rows.Count, _
ActiveCell.Column - 1).End(xlUp).Offset(0, 1)).FillDown
End Sub


So I could apply my macro somewhere, then have a macro that
copies and pastes it where I need to and somewhere in the maro
I pasted in the above formula? That would be sweet if that's all
there is to it.

Hi,

For a (simple) formula you can Copy the formula, then type the address of where you want it to go in the Name box (left of the fx formula bar) and Paste

ie
in B2 the formula =VLookup(A2,Sheet2!A$1:C$500,3,False)
select and Copy that,
put B3:B500 in the Name box and press Enter to select that range, then Paste.

Or you can 'drag' the + for a few rows etc.

That macro would be for filling in gaps in the range, something not normally done with a column formula (formula are generally written to apply to any cell in the range for which they are required).

but, let me know how you go.

---

[UnderTheBridge]

I just have to comment about how entertaining your write up was - hope you hang around.