counting the values in a column of cells

To anyone who can help!

I am trying to count up the values in a column of cells 
that contain formulas so i can give a sub total at the 
bottom of the sheet. I am trying to do this using vb.

Does anyone have the code to do this?

thanks, bob from long island, n.y.
0
rsalzer (1)
8/25/2003 3:33:30 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
209 Views

Similar Articles

[PageSpeed] 19

Sub countformulas()
mysum = 0
For Each c In Selection
If c.HasFormula Then mysum = mysum + 1
Next
MsgBox mysum
End Sub

"bob" <rsalzer@ureach.com> wrote in message
news:0b7201c36b1e$3c63f300$a501280a@phx.gbl...
> To anyone who can help!
>
> I am trying to count up the values in a column of cells
> that contain formulas so i can give a sub total at the
> bottom of the sheet. I am trying to do this using vb.
>
> Does anyone have the code to do this?
>
> thanks, bob from long island, n.y.


0
Don
8/25/2003 4:07:26 PM
Sub BuildSum()
  set rng = Cells(rows.count, Activecell.Column).End(xlup)(2)
  rng.formulaR1C1 = "=Sum(R1C:R[-1]C)"
  ' if you don't want the formula
  rng.Formula = rng.Value
End Sub


-- 
Regards,
Tom Ogilvy

"bob" <rsalzer@ureach.com> wrote in message
news:0b7201c36b1e$3c63f300$a501280a@phx.gbl...
> To anyone who can help!
>
> I am trying to count up the values in a column of cells
> that contain formulas so i can give a sub total at the
> bottom of the sheet. I am trying to do this using vb.
>
> Does anyone have the code to do this?
>
> thanks, bob from long island, n.y.


0
twogilvy (1078)
8/25/2003 4:15:33 PM
Reply:

Similar Artilces:

How to assign value to controls on a Report
I am trying to assign value on a txtbox control from a form to a txtbox control on a report. Form txtbox control:txtBeginDateRange Report txtbox control:txtStartDate reportName = "Report by DR and Vendor" I am have a problem with this line of code Reports![reportName]!txtStartDate = Me.txtBeginDateRange DoCmd.OpenReport reportName, acViewPreview Any help will be appreciated. Thank you. Ayo. Answered in another post where you asked the same question. ...

How to retreive deepest XPath value from XML using VB.NET
This is a multi-part message in MIME format. ------=_NextPart_000_000C_01C63D0B.9BCC4D30 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi All, Does anyone know how to retreive deepest XPath value from XML document = by using VB.NET? For example, if I had an XML file like this: <Root> <Customer> <Name>MyName</Name> </Customer> </Root> I would like to retreive "\Root\Customer\Name" out of it. Something = like: Dim xmlDoc As XMLDocument Dim strXPath As String =3D xmlDoc.GetXPa...

Stacking columns!!!
I have columns with headings like: NAME - AGE - EXPERIENCE. I have many sheets like this and I have to import all of them into on sheet -- where all the entries are stacked one below the other. There was already an enquiry about this in this forum -- but there wa no reply. If any has a solution, it would be appreciated. thanks. Subbu :confused -- kmsubb ----------------------------------------------------------------------- kmsubbu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=844 View this thread: http://www.excelforum.com/showthread.php?threadid=276...

Broken lines in a chart to display extreme values?
Sometimes a single extreme value causes the rest of the data to become too small, like a bar chart of the values (10000, 4, 3, 2, 4, 5). The way to avoid is to use broken lines where the axis makes a leap from 10 to 9990 with a dotted gap inbetween. I couldn't find a way to implement this in Excel. Is there a way? Hi, It is not a built in option but can be constructed. http://www.andypope.info/charts/brokencolumn.htm Cheers Andy Metin Örsel wrote: > Sometimes a single extreme value causes the rest of the data to become too > small, like a bar chart of the values (10000,...

Pasting values not formulas in Excel 2002
How does one copy and paste values rather than formulas in Excel 2002? I have Excel 97 and Excel 2000 and in both one can do this through Edit -> Paste Special But Paste Special does not give you this option in Excel 2002 and I cannot find any help in the online Help with about how to paste values. Hi Stephen, I use XL2002 and I can Copy --> Paste Special --> Values with n problem. Edit Paste Special Values Values is in the Paste Section in "Paste Special" and is the thir option on the left. I hope this helps you. Carlos Lopez -- Message posted from http://ww...

Column, Row Font Size
For some reason the column letters and the row numbers - at the very top and far left on any worksheet - have become so small on a particular worksheet as to be illegible. What have I done, and how do I undo it? If it only happens on one worksheet, maybe you've set the zoom too small. From the menu bar: View|Zoom... (and resize to look nice) There's a Zoom icon on the Standard toolbar that you could use, too. (My icon is at the far right of that toolbar.) Old Red One wrote: > > For some reason the column letters and the row numbers - at the very top and > far left ...

Number Automatically divided by 100 and stored in cell
Dear Friends, I am facing a problem. When I type say 1000 in an excel cell it apperas as 10 in that cell and in Formulabar ( the original value 1000 lost). Similarly 12345 appears as 123.45. I have checked the number formatting which is General. In each case after pressing Enter key the number is first divided by 100 and then result is stored in the cell. I could not find the reason why it is happening so. Please guide me in this regard. I will be very thankfull for your help. Muhammad Go to Tools/Options/Edit and uncheck 'Fixed decimal' Andy. "Muhammad Jawad Mirza&...

Automatic resizing of cell
I use Excel for my job. When I enter a long text sentence into a cell that is larger than the cell, it gets covered up because the text wrapping is on. I want the sentence to wrap around to the next line, but Excel doesn't automatically adjust the size of the cell when it sees that the text is longer. I end up printing out my work and only half of the sentence is there. I can resize it myself, but I want Excel to resize it when it sees that it's covering it. I can't see why anyone would actually WANT Excel to cover it up. Which version of Excel are you using. I'm u...

Help with column
I have a datafeed that comes to me everyday i need to format one column of around 1000 rows so it multiples the total column but a 1000 so getting rid of the decimal point i.e =A3100.00 becoming 10000 , i also need to do this using a macro. can anyone help=20 Regards=20 Paul Dim iLastRow As Long Dim i As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To iLastRow Cells(i, "A").Value = Cells(i, "A").Value * 100 Next i -- HTH Bob Phillips "lennymos123" <paul@palo.co.uk> wrote in message news:1115651206....

Only chart numerical values, not error values
In my data series I am using NA() in a formula so that #N/A shows up in the cell. From the website listed below I thought that this would not chart the error values but my charts are still conecting all my marks. Can Excel only show my MIN/MAX marks? =IF($B2=MAX($B2:$B$1562),$B2,NA()) http://peltiertech.com/Excel/Charts/FormatMinMax.html Thanks! Hi, If you have #N/A in the cell then the point will not be plotted. But if there is a valid data point(s) after it then the line will be plotted to join those points interpolating the gap in between. It will not leave put a break in the line....

Blank line in cells
I am having trouble with the autofit row height in Excell XP. It seems that when my last line of text in a cell is up to 5 characters short of the end of the cell Excel automatically enters another line as if the previous line was to long. I know a work around would be to manually adjust the height, but I could find myself in trouble if text were added to those cells. This is a true Excel annoyance. I don't think there is a way to get the right row height and keep the autofit working. -- Jim "Dan" <Dan@discussions.microsoft.com> wrote in message news:A5AB59D0-34...

How to show a leading 0 in a cell.
I can't remember the tip of how to show a leading zero in a cell. For instance a zip code of 01234. I know there's a key you can hit and the zero will show and not disappear when you go to the next cell. Anyone know it? use text or ' before puttin in or format as 00000000000 (as many as you want to show) -- Don Guillett SalesAid Software donaldb@281.com "PAtty" <PAtty@discussions.microsoft.com> wrote in message news:7C3D35FC-061E-4B56-BCFF-4ED2E843D648@microsoft.com... > I can't remember the tip of how to show a leading zero in a cell. For > instance...

Select not actualy selecting the cell
I need to fix the same problem in alot of workbooks. Its just a mater of centering the same cell in each workbook. All workbooks are layed out the same and have one worksheet so I thought it would be pretty simple. however, I noticed that the only workbook being acted upon was the selected workbook even though the print.debug listed each workbook. for lack of nothing else comming to mnd I added the "For Each ws In Worksheets" but that was not help. Any help will be appreciated Thanks Public Sub CtrZX1Cell() 'centers the cell to keep printing on one sheet Dim ...

too many different formatting cells
I can't open an excel document because whem I try to open it says that too many different formatting cells. How to resolve this and open this document? Maybe... XL: Error Message: Too Many Different Cell Formats http://support.microsoft.com/default.aspx?scid=213904 A few people have said that OpenOffice.Org has been able to open the file. Then they clean it up and save it there. Then excel can open that cleaned up version. http://www.openoffice.org, a 60-104 meg download or a CD jo wrote: > > I can't open an excel document because whem I try to open it says that too >...

Use cell value as cell address
Hello everyone. I have a worksheet "Main" of 39,000 rows in which column B contains a number between 1 and 7,500. Column C is an empty column I have added. The second sheet, "Names" in the book contains a single column - A - of 7,500 names. I want to get the value from the second sheet that matches the number column of the first sheet. In other words, if "Main" cell B3 contains 3780, I want to put the value from "Names" cell A3780 into "Main" C3. How do I do this please? Richard --- Message posted from http://www.ExcelForum.com/ Hi tr...

All Columns are not to move
How can I stop the columns from moving to the left of the Table? -- Roger On Sun, 3 Feb 2008 17:28:39 -0800, Roger <Roger@discussions.microsoft.com> wrote: >How can I stop the columns from moving to the left of the Table? You'll have to explain what on Earth you're talking about, Roger. What columns are "moving"? How? John W. Vinson [MVP] Good evening John, I found my own answer:by bringing the cursor to the first cell, the first column will not scroll to the left anymore; however, when I bring the cursor to any other cell and scroll to the rig...

Add value when recording a macro
Hello, I recorded a macro to create a bar chart - I checked the checkbox to add the values. Stopped recording and showed me the chart with the values. If I run the macro again, it does not display the value. What is the code I need to always display the value? -- LizW After you create the chart, turn on the macro recorder again Select the chart, and choose Chart>Chart Options On the Data Labels tab, check Values Click OK Turn off the macro recorder In the recorded code, you'll see a line similar to this: ActiveChart.ApplyDataLabels AutoText:=True, LegendKey:=False, _ Ha...

Text-To-Columns Fixed Width
When using Text-To-Columns, Excel "recognizes" that the data fits the Fixed Width criteria and PRE-assigns the width. In most of the cases I work with Excel is wrong 99% of the time. Is there a way to force Excel to NOT pre-assign the width (leave everything blank)? On the first panel of the wizard click Delimited, then on the second panel click space as the delimiter. Hope this helps. Pete On Feb 4, 12:50=A0am, TP <T...@discussions.microsoft.com> wrote: > When using Text-To-Columns, Excel "recognizes" that the data fits the Fix= ed > Width ...

passing non-field value into vba
I need to pass the line item sequence number (lnitmseq) from the sales item detail entry window into vba. This isnt a visible field in the window, so i cannot "add field to vba" using the TOOLS --> customize toolbar window. Anyone have any idea how this can be done? Using the Modifier you can make the field visible, add it to VBA and then remove the modified window or make the field invisible again. While in the Window Layout mode, make sure that the Invisible fields are shown (Layout >> Show Invisible Fields) and then change the Visual Property, Visible = true and mov...

display the value of an unbound textbox in a field within a table
I created 3 textboxs to calculate the number of business days between 2 dates. The 3rd textbox contains the value. I now want to have this value displayed in a table field. I'm sure this is easy but I'm a complete novice and have spent far too much time on this already. Can anyone help! Fibi, The general concept of tables is that they are for storage of data in the background. They are not for display of data. That is what forms and reports are for. Therefore, the appearance of the data in the tables is not normally relevant, and the display of calculated values is im...

Sort column with first and last name by last name
Hello, I have a mailing list with 10,000 names. The first cell has first an last name in the same cell. First name is listed first. Is there formula to sort and/or separate text in a cell. I would like to sort o separate the first name from the last in the cell to allow for a mai merge by name. Using Excel 2002. Thank you -- Message posted from http://www.ExcelForum.com If you have just first and last names separated by a space, Data>Text to Columns would be the easiest method to split into two columns. If more than that, like names with van or von or de etc. you may need a different m...

how to program recurring value
I wish every entry of a colums to be a the same function of the entry above. How do I do that? Leo Hi Leo! Assuming that you have your first formula entered Select the range covered by the common entry formula F2 Ctrl + Enter -- -- Regards Norman Harker MVP (Excel) Sydney, Australia Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "Leo Kerner" <l.kerner@sympatico.ca> wrote in message news:404294EF....

pasting or moving formula cells without updating formulas
I have a flat spreadsheet with a results page at the end. The results page contains a set of formulae which refer to various cell locations within the body of the spreadsheet in order to return statistical results based on the values in said cells. Now I'd like to add more data to my spreadsheet, so i need to make it bigger; however, when I copy and paste, or select and drag the cells containing the formulae, Excel updates the formulae so that they refer to different cells which bear the same spatial relationship to the formulae as the original referees did before the formulae were ...

deleting duplicate cells
I am back again... Thanks to everyone's help here last time, I was able to finish all my work and do it correctly. Ken- I asked last time i was here about deleting duplicate cells. Some of the names(address, etc) are repeated in my sheet. I want to delete the extra cell of the people who are in here twice. Not jus the cell but their record, name, address, city, state zip when they are in their twice so that they will only be listed once. You told me how to do this once but i cant find where it is on the board. Thanks for all the help.... BR4 -----------------------------------------...

Counting Null Values in a Report
I have a report that is grouped by people, then by Reason Closed. I want to count how many entries do not have a closed date. I tried the previous posts but could not get it to work. I have a group header for each person to group their categories together. Thanks. Hi Dea, Try: =Sum(-IsNull([ClosedDateFieldName])) Note the minus sign just after the first paranthesis. IsNull() returns a -1 when the item is null, otherwise 0. So by summing up the negative of each (-1)s you are in essence counting 1 for each null item. Alternatively you could move the minus...