I have almost 25,000 rows of data and I'm a little scared LOL

[aaaa]

Firstly I'm a total newb to excel (and access for that matter LOL)
I have almost 25,000 rows of data and I'm a little scared LOL

its a basic set up, Total, Passed, Rejected ect ect across the top with the
info below.

example...

Date Total Passed Rejected
1/2/05 123 100 23
1/4/05 324 320 4
1/6/05 97 96 1

I need a fifth column showing a percentage of passed.
but I don't know how to do this with out entering the formula 25 thousand
times :-(

Please help

Also would really love to be pointed towards an easy to understand
instruction site for access.

Thank you all,

Chad

 

[Don Guillett]

try this which does the formula and removes it
Sub addformulasandtakeaway()
x=cells(rows.count,"a").end(xlup).row
Set frng = Range("e2:e" & x)
With frng
.Formula = "=c2/d2"
.Formula = .Value
End With
End Sub

 

[aaaa]

Thanks for the fast reply Don

LOL where should enter this?
does it matter what cell is selected?
I thought I would be able to make out what I need to change to make this
work for my sheet, but I was WAY wrong LOL
I should have been more specific sorry
total is in column C and Passed is column G and would like the % in column J
what would I need to change to make this work?

 

[Don Guillett]

The modification of the macro should be fairly obvious.
Modify>put it in a module and execute from alt f8 or assign to a button.

 

[Debra Dalgleish]

Select cell E2, and enter the formula: =C2/B2
Format the cell as Percent
With cell E2 selected, point to the Fill Handle -- the small black
square at the bottom right of the selection.
When the pointer changes to a small black plus sign, double-click,
to fill the formula down to the first blank row.

 

[David McRitchie]

You would probably find it faster to locate the last row, ctrl+End
should help with that. then after entering the formula into E2
as described or something that ignores division by zero you
could copy it down by typing E2:E25000 (if that is then last cell wanted)
then use the shortcut Ctrl+D

More information on use of the fill handle and use of Ctrl+D
http://www.mvps.org/dmcritchie/excel/fillhand.htm

If Ctrl+End took you way beyond your actual data you might want to
fix that see the macro (don't do it manually) at
Why do my scrollbars go to row 500 -- my data ends in cell E50?, contextures.com, Debra Dalgleish
http://www.contextures.com/xlfaqApp.html#Unused

As far as the macro supplied earlier, if you still don't know how to install
a macro see http://www.mvps.org/dmcritchie/excel/getstarted.htm
you would need to install a macro if your last cell is way beyond your
data and you want to use a macro to fix all sheets at once.

 

[SteveW]

Select cell E2, and enter the formula: =C2/B2
Format the cell as Percent
With cell E2 selected, point to the Fill Handle -- the small black
square at the bottom right of the selection.
When the pointer changes to a small black plus sign, double-click,
to fill the formula down to the first blank row.

dragging 25,000 is a real pain
click on e2
Ctrl+C to copy this cell

Ctrl+End will take you down to the bottom corner
Click on the last cell in E that you want the formula in
Hold Shift
Ctrl+Home will take you back to to the top, use the cursor
to move *top* of this block to e3
Paste in the formula - Ctrl+V

Messy, but after a while that sort of Block selection gets easier,
whereas I never get used to dragging 25,000 lines !

 

[David McRitchie]

should read
then after entering the formula into E2: =C2/B2 and then
typing E2:E25000 into the name box to the left of the address bar
then use the shortcut Ctrl+D

--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

You would probably find it faster to locate the last row, ctrl+End
should help with that. then after entering the formula into E2
as described or something that ignores division by zero you
could copy it down by typing E2:E25000 (if that is then last cell wanted)
then use the shortcut Ctrl+D

More information on use of the fill handle and use of Ctrl+D
http://www.mvps.org/dmcritchie/excel/fillhand.htm

If Ctrl+End took you way beyond your actual data you might want to
fix that see the macro (don't do it manually) at
Why do my scrollbars go to row 500 -- my data ends in cell E50?, contextures.com, Debra Dalgleish
http://www.contextures.com/xlfaqApp.html#Unused

As far as the macro supplied earlier, if you still don't know how to install
a macro see http://www.mvps.org/dmcritchie/excel/getstarted.htm
you would need to install a macro if your last cell is way beyond your
data and you want to use a macro to fix all sheets at once.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Select cell E2, and enter the formula: =C2/B2
Format the cell as Percent
With cell E2 selected, point to the Fill Handle -- the small black
square at the bottom right of the selection.
When the pointer changes to a small black plus sign, double-click,
to fill the formula down to the first blank row.

 

[Tushar Mehta]

Re-read Debra's post. There's no dragging involved, just one double-click!

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Peo Sjoblom]

dragging 25,000 is a real pain
click on e2
Ctrl+C to copy this cell

Ctrl+End will take you down to the bottom corner
Click on the last cell in E that you want the formula in
Hold Shift
Ctrl+Home will take you back to to the top, use the cursor
to move *top* of this block to e3
Paste in the formula - Ctrl+V

Messy, but after a while that sort of Block selection gets easier,
whereas I never get used to dragging 25,000 lines !

But it is hardly faster than double clicking lower right corner of E2 as
Debra suggested?

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

 

[Tushar Mehta]

I'm intrigued how anything could be faster than one double-click in the top
cell...

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

You would probably find it faster to locate the last row, ctrl+End
should help with that. then after entering the formula into E2
as described or something that ignores division by zero you
could copy it down by typing E2:E25000 (if that is then last cell wanted)
then use the shortcut Ctrl+D

More information on use of the fill handle and use of Ctrl+D
http://www.mvps.org/dmcritchie/excel/fillhand.htm

If Ctrl+End took you way beyond your actual data you might want to
fix that see the macro (don't do it manually) at
Why do my scrollbars go to row 500 -- my data ends in cell E50?, contextures.com, Debra Dalgleish
http://www.contextures.com/xlfaqApp.html#Unused

As far as the macro supplied earlier, if you still don't know how to install
a macro see http://www.mvps.org/dmcritchie/excel/getstarted.htm
you would need to install a macro if your last cell is way beyond your
data and you want to use a macro to fix all sheets at once.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Select cell E2, and enter the formula: =C2/B2
Format the cell as Percent
With cell E2 selected, point to the Fill Handle -- the small black
square at the bottom right of the selection.
When the pointer changes to a small black plus sign, double-click,
to fill the formula down to the first blank row.

 

[David McRitchie]

Okay Peo and Tushar, missed that it was a double click, but whenever
using that you do want to verify that you covered the range as it will stop
if there is an empty cell to the left (in this case).