Posts by captbluefin

  • summing numbers only in a column containing numbers and text

    We are actually looking for two solutions to the same problem


    1) How can you write code in a macro that will calculate the sum of a column that contains both numbers and text and display the result in a cell.


    We have written a macro that uses brute force to look at every cell to see if it contains text or not.
    Not a good procedure when the column contains many cells.



    2) Is there an easy way to way to format the cell itself to display the sum of a column that contains text.



    Ex.
    A
    1 7
    2 ND
    3 2
    4 ND
    5


    How can you display the sum of A1..A4 in A5 while ignoring the cells that contain text??



    Thanks

  • Sharing a workbook

    Hi,


    Lot's of macros. They all work when the wokbook is not shared. The one we are having problems with is extremely long.


    I wonder if I go into debug mode if I will be able to find where I get kicked out?


    BB

  • Sharing a workbook

    We are an environmental lab. We log samples, print reports, and save reports with excel. Should have used a database - too late now.


    Unfortunatelythe program stops with the error, but we don't know where the error is.

  • Sharing a workbook

    We would like to share a workbook and it's macros. We have no problem sharing the workbook or the macros.


    We are running into the following error: command not available in a shared workbook.


    Any help making all commands available would be appreciated.


    Thanks,


    BB

  • Test for file existence

    I am writing an excel macro that tests to see if a file is present on the hard drive. If the file exists, I would like a MsgBox to pop up saying "Yes". I am having trouble with creating an object and also with data type mismatches. Any help would be appreciated. Using excel 2003.


    Thanks

  • Significant Figures

    HELP Once again I am driving myself insane.
    We need to format cells in our worksheet to show significant figures.


    We need to post lab results using the correct number of significant figures.


    Ex.


    0.1 must be displayed as 0.100


    10 needs to be displayed as 10.0


    105 as 105
    1023 as 1020
    10343 as 10300
    165435 as 165000


    I have written a complicated if then else statement for every cell. This statement is in the cell, not vbscript. Selection.numberformat statement will only work using vbscript.


    The statment works perfectly EXCEPT when a number is an exact number.


    The problem is that when some of the trailing digits are 0's they get truncated.


    We always want numbers less than 1 to show 3 decimal places
    Numbers between 1 and less than 10 should show 2 decimal places.
    Numbers between 10 and less than 100 should show 1 decimal place.


    My statement works fine for numbers 100 and above.
    My statement also works fine for numbers less than 100 when there are no trailing zeroes.


    10.2 shows as 10.2 but 10.0 shows as 10



    Please - if you are familiar with significant figures and know of a solution, I would be greatly appreciative of any help

  • [Solved] VBA: restrict find to one row only - look nowhere e

    We have a worksheet with the following layout:

    0 A B C D
    1 5 X Y Z
    2 6 Y Z X
    3 7 Z X Y



    How can I use find to select cell b2 only.
    Something like


    Find.Cell (What:= "6").activate
    Find.Cell (What:= "Y").activate
    ActiveCell.Select


    The only cell that could ever be selected is B2


    I've tried everthing I can think of to restrict the search to ONLY the row where the first find statement locates the find criteria.


    Any help would be greatly appreciated!


    Thanks