+ Reply to Thread
Results 1 to 6 of 6

Evaluate each cell in a table column - quickest method?

  1. #1
    Forum Contributor
    Join Date
    03-05-2007
    Location
    Falmouth, VA now, Palm Bay, FL for 2 yrs, was Colorado Springs, CO for ten years; Cedark Park, TX; Zeeland, MI; Wilmette, IL; Princeton Junction, NJ; NY, NY
    MS-Off Ver
    365
    Posts
    583

    Evaluate each cell in a table column - quickest method?

    Though I could get the row number of the top and bottom rows in a table (a formal table created by Insert:Table) or get the number of rows and then do a For/Next loop with a counter for each cell, is there a more efficient way to run through all the cells in a table column?

    I need to verify that each cell has numerical data in it, if not then I either need to
    1. extract it to numerical if it is a number that has been formatted as text or has any mistakes with leading or trailing text,
    2. otherwise change the entry to a "0".

    Kind of wondering if there is a way similar when evaluating collections such as worksheets to using
    Please Login or Register  to view this content.
    where ws has already been defined as a worksheet type, other than making a class somehow out of the cells in the column!

    Thank-you!!!

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,895

    Re: Evaluate each cell in a table column - quickest method?

    Maybe something like this:
    Please Login or Register  to view this content.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Contributor
    Join Date
    03-05-2007
    Location
    Falmouth, VA now, Palm Bay, FL for 2 yrs, was Colorado Springs, CO for ten years; Cedark Park, TX; Zeeland, MI; Wilmette, IL; Princeton Junction, NJ; NY, NY
    MS-Off Ver
    365
    Posts
    583

    Re: Evaluate each cell in a table column - quickest method?

    As always, THANK-YOU!
    (Steamboat Springs? It's hard enough finding work in Colorado Springs if one is not military!)

  4. #4
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Evaluate each cell in a table column - quickest method?

    Tables are very easy to loop through once you know the syntax. You can loop through just the data in a column without worrying about header rows or totals rows at the bottom.
    Here's a link you might be interested in.
    Last edited by skywriter; 12-30-2015 at 04:27 PM.
    Click the * Add Reputation button in the lower left hand corner of this post to say thanks.

    Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,895

    Re: Evaluate each cell in a table column - quickest method?

    @brucemc777

    Retired. Working on the mountain and Skiing regularly. Excel work? Not here!!

    @skywriter

    Excellent post on Tables.
    Last edited by alansidman; 12-30-2015 at 04:44 PM.

  6. #6
    Forum Contributor
    Join Date
    03-05-2007
    Location
    Falmouth, VA now, Palm Bay, FL for 2 yrs, was Colorado Springs, CO for ten years; Cedark Park, TX; Zeeland, MI; Wilmette, IL; Princeton Junction, NJ; NY, NY
    MS-Off Ver
    365
    Posts
    583

    Re: Evaluate each cell in a table column - quickest method?

    @skywriter: Thanks!
    @alansidman: Congrats!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 03-24-2013, 05:57 PM
  2. quickest method for dynamic link to closed workbook
    By bangelta in forum Excel General
    Replies: 0
    Last Post: 10-25-2012, 02:53 PM
  3. A More Efficient Method Than the Evaluate Method?
    By anthony.mcgovern in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 12-22-2011, 05:39 AM
  4. IF Statement Formula Help(Quickest Method)
    By vargs in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-24-2010, 11:46 AM
  5. multiple averages, long datasheet-quickest method?
    By CastonjoFfF in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-11-2007, 03:38 PM
  6. VB evaluate a value in a table's column and display msgbox
    By AusTexRich in forum Excel General
    Replies: 8
    Last Post: 10-10-2005, 06:05 PM
  7. What is the quickest method to insert & name multiple worksheets .
    By clyonesse in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-20-2005, 06:05 PM
  8. What is the quickest method to insert & name multiple worksheets .
    By clyonesse in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-20-2005, 04:11 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1