C
CompleteNewb
There are a lot of complications outside of this, but I'm handling them so
far and am stuck right here:
A machine outputs results from testing different items for levels of
different contaminants. Not every item is tested for the same contaminants.
On sheet 1 I have this:
Item 1
Lead 3Mg
PCBs 2Mg
Nylon 3Mg
Non Item
stuff to ignore
Stuff to ignore
Stuff to ignore
Item 2
PCBs 1Mg
Rubber 2Mg
Nylon 1 Mg
On sheet 2 I have a list in column A of all contaminants that can possibly
be tested:
PCBs
Lead
Nylon
Rubber
What I need to do is take every item (as opposed to non item) on Sheet 1,
and put the results for it on Sheet 2, like so:
Item1 Item2
PCBs 2mg 1mg
Lead 3Mg
Nylon 3Mg 1Mg
Rubber 1Mg
Right now I'm using VBA to find "item" in Sheet 1, then I'm looping through
the cells under Item until they stop (are empty) to fill an array with the
contaminants it was tested for (by the way, I DO have to do this; aside from
the "Stuff to ignore" there are a bunch of other complications, but I'm
handling them well enough to get to where I am in this example). Then I'm
going to sheet 2, going to the next blank cell in my header row, putting
"Item 1". So far so good, including skipping past ignored stuff. Now, my
plan was to, for every contaminant in the current array, find a match for it
in column A of sheet 2, and place the value from Sheet 1 for this item in
the Item 1 column in the appropriate cell, do that until I had gotten to end
of array, then proceed with next Item (going back to Sheet 1, finding next
Item, building array again, etc.). I was trying to muddle my way through
all the different Application.Match, Application.Index, Application.Vlookup
stuff I was going to have to piece together to do this, but all the stuff
I've been seeing in vba indicates using Application.[whatever] is not the
best way to go about these things. So I started thinking, should I build an
array that's the contaminant AND the value first? And if I did that, I'm
not sure how to find a match for the 1st element of the arry (contaminant)
in Column A, then put the value from the 2nd element (the Mg result) in the
right row under my current Item column, etc. This seems like a simple thing
to do, but all the stuff I had to do up until now to get to this small piece
has me wondering if I'm overcomplicating this small piece.
Any advice on a quicker/more reliable way to do this, or how to get it done
the array route I'm in, would be greatly appreciated. I am totally NOT
married to the array approach; I was doing that because the data in sheet 1
is not evenly spaced or always in the right spot, and some specific items
are handled in different ways, so I was finding my Item first, then getting
my array filled so I could perform my operation then proceed. If a better
way would just be to use the range that my values are in, without having to
build an array, I can do that too (I already have the range assigned for
when I build the array). Or, of course, some other way that's more reliable
or simpler.
Again, I appreciate your time and consideration.
far and am stuck right here:
A machine outputs results from testing different items for levels of
different contaminants. Not every item is tested for the same contaminants.
On sheet 1 I have this:
Item 1
Lead 3Mg
PCBs 2Mg
Nylon 3Mg
Non Item
stuff to ignore
Stuff to ignore
Stuff to ignore
Item 2
PCBs 1Mg
Rubber 2Mg
Nylon 1 Mg
On sheet 2 I have a list in column A of all contaminants that can possibly
be tested:
PCBs
Lead
Nylon
Rubber
What I need to do is take every item (as opposed to non item) on Sheet 1,
and put the results for it on Sheet 2, like so:
Item1 Item2
PCBs 2mg 1mg
Lead 3Mg
Nylon 3Mg 1Mg
Rubber 1Mg
Right now I'm using VBA to find "item" in Sheet 1, then I'm looping through
the cells under Item until they stop (are empty) to fill an array with the
contaminants it was tested for (by the way, I DO have to do this; aside from
the "Stuff to ignore" there are a bunch of other complications, but I'm
handling them well enough to get to where I am in this example). Then I'm
going to sheet 2, going to the next blank cell in my header row, putting
"Item 1". So far so good, including skipping past ignored stuff. Now, my
plan was to, for every contaminant in the current array, find a match for it
in column A of sheet 2, and place the value from Sheet 1 for this item in
the Item 1 column in the appropriate cell, do that until I had gotten to end
of array, then proceed with next Item (going back to Sheet 1, finding next
Item, building array again, etc.). I was trying to muddle my way through
all the different Application.Match, Application.Index, Application.Vlookup
stuff I was going to have to piece together to do this, but all the stuff
I've been seeing in vba indicates using Application.[whatever] is not the
best way to go about these things. So I started thinking, should I build an
array that's the contaminant AND the value first? And if I did that, I'm
not sure how to find a match for the 1st element of the arry (contaminant)
in Column A, then put the value from the 2nd element (the Mg result) in the
right row under my current Item column, etc. This seems like a simple thing
to do, but all the stuff I had to do up until now to get to this small piece
has me wondering if I'm overcomplicating this small piece.
Any advice on a quicker/more reliable way to do this, or how to get it done
the array route I'm in, would be greatly appreciated. I am totally NOT
married to the array approach; I was doing that because the data in sheet 1
is not evenly spaced or always in the right spot, and some specific items
are handled in different ways, so I was finding my Item first, then getting
my array filled so I could perform my operation then proceed. If a better
way would just be to use the range that my values are in, without having to
build an array, I can do that too (I already have the range assigned for
when I build the array). Or, of course, some other way that's more reliable
or simpler.
Again, I appreciate your time and consideration.