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

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

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

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"aaaa" <not@spam.com> wrote in message
news:dpudnRVarMdswYHZRVn-rw@comcast.com...
> 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
>

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" <dguillett1@austin.rr.com> wrote in message
news:OYntDNsSGHA.2276@tk2msftngp13.phx.gbl...
> 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
>
> --
> Don Guillett
> SalesAid Software
> dguillett1@austin.rr.com
> "aaaa" <not@spam.com> wrote in message
> news:dpudnRVarMdswYHZRVn-rw@comcast.com...
>> 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
>>
>
>

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.

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"aaaa" <not@spam.com> wrote in message
news:2qCdndJC7Pp3-4HZnZ2dnUVZ_uWdnZ2d@comcast.com...
> 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" <dguillett1@austin.rr.com> wrote in message
> news:OYntDNsSGHA.2276@tk2msftngp13.phx.gbl...
>> 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
>>
>> --
>> Don Guillett
>> SalesAid Software
>> dguillett1@austin.rr.com
>> "aaaa" <not@spam.com> wrote in message
>> news:dpudnRVarMdswYHZRVn-rw@comcast.com...
>>> 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
>>>
>>
>>
>
>

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.

aaaa wrote:
> 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
>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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

"Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message news:441C71EE.60702@contexturesXSPAM.com...
> 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.
>
> aaaa wrote:
> > 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
> >
> >
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>

On Sat, 18 Mar 2006 15:47:42 -0500, Debra Dalgleish
<dsd@contexturesXSPAM.com> wrote:

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

>


>aaaa wrote:
>> 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
>>
>>

--

Steve

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

"David McRitchie" <dmcritchie_xlmvp@verizon.net> wrote in message news:e6w5xstSGHA.5468@TK2MSFTNGP14.phx.gbl...
> 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
>
> "Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message news:441C71EE.60702@contexturesXSPAM.com...
> > 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.
> >
> > aaaa wrote:
> > > 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
> > >
> > >
> >
> >
> > --
> > Debra Dalgleish
> > Excel FAQ, Tips & Book List
> > http://www.contextures.com/tiptech.html
> >
>
>

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

In article <pc4p12pimd807gr7ad2dsllsdfqtn3hnf2@4ax.com>, sj_w@nothotmail.com
says...
> On Sat, 18 Mar 2006 15:47:42 -0500, Debra Dalgleish
> <dsd@contexturesXSPAM.com> wrote:
>
> >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 !
>
> >
>
>
> >aaaa wrote:
> >> 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
> >>
> >>
>
>

"SteveW" <sj_w@nothotmail.com> wrote in message
news:pc4p12pimd807gr7ad2dsllsdfqtn3hnf2@4ax.com...
> On Sat, 18 Mar 2006 15:47:42 -0500, Debra Dalgleish
> <dsd@contexturesXSPAM.com> wrote:
>
>>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 !

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

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

In article <e6w5xstSGHA.5468@TK2MSFTNGP14.phx.gbl>,
dmcritchie_xlmvp@verizon.net says...
> 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
>
> "Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message news:441C71EE.60702@contexturesXSPAM.com...
> > 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.
> >
> > aaaa wrote:
> > > 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
> > >
> > >
> >
> >
> > --
> > Debra Dalgleish
> > Excel FAQ, Tips & Book List
> > http://www.contextures.com/tiptech.html
> >
>
>
>

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