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

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

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!