+ Reply to Thread
Results 1 to 7 of 7

"Read" the column header "Element" and perform calculation

  1. #1
    Registered User
    Join Date
    11-06-2012
    Location
    Brisbane, Austalia
    MS-Off Ver
    Excel 2010
    Posts
    21

    "Read" the column header "Element" and perform calculation

    Hello VBA Gurus,

    I get outputs from a program that gives me values in mol but I want mg.
    The column headers could be any order and any length and contain red-herrings (things that are not elements).
    I am trying to build a macro that searches the column header, identifies which element (if any) it refers to, then performs the appropriate conversion to all the values in that column (which may be of any length), then moves on to the next column.

    I have a collection of the elements and their atomic masses but I am stuck in the weeds manipulating collections and arrays.

    Attached is example input file.

    I want to multiply each value in the column by the corresponding atomic mass and then by 1000.

    Here is my code (I haven't got very far!):
    Please Login or Register  to view this content.
    Thanks in advance, I have been using this site for a long time and it's a great thing!
    Attached Files Attached Files

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,255

    Re: "Read" the column header "Element" and perform calculation

    Hello dwsf,

    Welcome to the Forum!

    There is a problem with the Excel file you attached. I am unable to open it. Please upload the file again and be sure your extension is correct for the version of Excel you are using.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    11-06-2012
    Location
    Brisbane, Austalia
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: "Read" the column header "Element" and perform calculation

    Hi Leith,
    Thanks for the welcome- hopefully attachment works this time, it was a CSV previously so that might have been a problem.
    Dave
    Attached Files Attached Files

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,255

    Re: "Read" the column header "Element" and perform calculation

    Hello dwsf,

    Thanks for uploading the file again. Now it opens with no problems.

  5. #5
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,255

    Re: "Read" the column header "Element" and perform calculation

    Hello dwsf,

    I didn't forget about you. It was late night, my time, when I replied. I was out all day today.

    This macro uses a Dictionary object instead of a collection. The Dictionary has several added features that Collections don't have but I won't go into to detail about that here.

    The attached workbook has a second sheet added to it named "Elem Data". This is a listing of the elements and their masses. This is easier to update than a long array in your macro. A range's values can be read vary easily and quickly into an array using the macro. In the long run, this will make your life easier.

    I have annotated the macro to help you better understand what it is doing. Using arrays to manipulate Excel data is always faster than going cell by cell. Turning off screen updating helps speed up the macro as the screen is updated only once after all values have been updated. This also eliminates screen flicker.

    Here is the macro that has been added to the attached workbook...
    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    11-06-2012
    Location
    Brisbane, Austalia
    MS-Off Ver
    Excel 2010
    Posts
    21

    Talking Re: "Read" the column header "Element" and perform calculation

    Hi Leith,

    This is fantastic! Thank you so much!

    I have created the dictionary in VBA using the Dict.add "element","Key" and set it to operate on the first sheet (which will always be the sheet of concern) so that it's a stand-alone solution-in-a-macro.

    It's exactly the solution I've been seeking and it will drastically reduce the tedium in my life.

    I really appreciate the annotations to help me with my understanding.

    This forum is a great resource due to its knowledgeable and altruistic contributors, thanks!

  7. #7
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,255

    Re: "Read" the column header "Element" and perform calculation

    Hello dwsf,

    You're welcome. Glad I could help you solve this problem.

+ 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. [SOLVED] Replace all BLANK cells in column with header title "Balance" to "0"
    By ks100 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-21-2014, 09:25 AM
  2. Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  3. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  4. [SOLVED] If there is any text in column "A$" on "sheet1" then move cell to column "A$" on "sheet2"
    By ckgeary in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-27-2013, 08:28 PM
  5. Replies: 2
    Last Post: 06-06-2013, 12:45 PM
  6. [SOLVED] Formula needed to display "Pass" or "Fail" if a column contains any values other than "yes
    By andreindy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-26-2013, 05:49 PM
  7. Replies: 1
    Last Post: 07-16-2010, 02:44 AM
  8. Replies: 0
    Last Post: 07-09-2009, 04:07 PM

Tags for this Thread

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