Kind of a "reverse" Vlookup; or please help me use Find instead
 

On Sheet3, I have a current range set at B3:B10 (it'a actually a range
object variable, like sht3BRange, but it's those cells). In Column A is a
list of values that I need to look up in a range in Sheet1, and where there
is a match, put the value from 3 cells to the right in Sheet1's range into
my sht3BRange in the right spot.

Here's the complication, though; I need to do this from Sheet1, I can't
start from Sheet3.

A brief overview:

Sheet3 doesn't exist at first. Sheet1 has a bunch of test reults that are
related in groups, but aren't all in the same groups:

Test1-A
Salt 3
Chlorine 5

Test1-B
Iron 2
Calcium 4

Test2-A
VitC 1
Iron 3

So Test1-A and Test1-B are my related groups. My Sheet3 will (hopefully)
have:

Mineral Test1 Test2
Iron 2 3
VitC 1
Calcium 4
Chlorine 5
Salt 3


So I write Sheet3 as I get Tests from Sheet1, but I have to group them
(Test1's, regardless of the -[letter] all go together, and they aren't
necessarily in order, either. It's possible Sheet 1 will have a Test1-A,
then a Test2-A, then a Test1-B, and there could be any number of related
test groups (A, B, C, etc.).

My Sheet3 Starts with populating column A with all items tested, across all
groups. Then what I'm doing is using Find in Sheet1 to go down the column
that has the Test headers (Test1-A, Test1-B, etc.), and where there is
Test1, then I'm setting a range that includes the values underneath, and
then I to go into Sheet3, make my new Test1 column header, and find a match
in the 1st column of Sheet3 for the 1st column in my Sheet1's temporary
range , and put the related value in my Sheet3's Test1 column. Then I Find
the next Test header in Sheet1, go through THAT range, put the values in
Sheet3 under Test1, etc.

Right now I use Find in Sheet 1 to get my 1st Test Header, getting the
range, and then using Vlookup to find a match from my range in Sheet3's
Column A. Where I'm stuck is, since I'm not doing the Vlookup from Sheet3,
I can't figure out how to put my data in the right row where the match was.
What I have so far is:

For Each cell In MySheet1Range '(which I just made after finding "Test1")
If Not IsError(Application.VLookup(cell.Value,
SHeet3Column1Range, 1, False)) Then

Sheet3.Cells(cell.Row,
Range(MyTest1Column).Column).Value = Application.VLookup(cell.Value,
MySheet1Range, 3, False)
End If
Next cell

I get a 1004 error at the Sheet3.cells line.

1) Am I doing this in a really dumb way? I'm writing the Sheet3 as I go,
it's not already built, so that's why I'm not doing Vlookups from there;
When I go through Test Headers in Sheet1 with my Find, if there's a Test1,
then I make the Test1 column in Sheet3, and then I have to put the values in
from the (1 or several) Test1 groups in Sheet1

2) If I'm not doing it in a dumb way, I think part of my problem is that
I'm using the cell.row to get the row in Sheet3, but it's using the cell.row
from Sheet1, and I can't figure out how to get the right row the match is in
Sheet3

Thanks for any help on this. I've used Find a little bit, but can't figure
out how to apply it here instead of Vlookup.
I appreciate any help

Kind of a "reverse" Vlookup; or please help me use Find instead
 

Okay, I got this working, but looking at it I'm guessing there's a MUCH
better way:

For Each cell In Sheet1Range
If Not IsError(Application.VLookup(cell.Value,
Sheet3ColumnARange, 1, False)) Then

Cells(Sheet3ColumnARange.Find(cell.Value,
LookIn:=xlValues, LookAt:=xlPart).Row, MyTest1Range.Column).Value =
Application.VLookup(cell.Value, Sheet1.Range(StartRange, EndRange), 5,
False)
End If
Next cell

So for all cells in my Range in Sheet1, finds a match for it in Sheet3's
Column A, then finds what ROW in Sheet3 the current Sheet1 Range's cell
value was found, then puts the VALUE from my Sheet1's Range in the Test1
column, in the row where the match found on Sheet3.

Pretty lame. It works though, so I posted it here. If someone has a better
way, I always appreciate feedback and learning how to do things better.


"CompleteNewb" wrote in message
...
On Sheet3, I have a current range set at B3:B10 (it'a actually a range
object variable, like sht3BRange, but it's those cells). In Column A is a
list of values that I need to look up in a range in Sheet1, and where
there is a match, put the value from 3 cells to the right in Sheet1's
range into my sht3BRange in the right spot.

Here's the complication, though; I need to do this from Sheet1, I can't
start from Sheet3.

A brief overview:

Sheet3 doesn't exist at first. Sheet1 has a bunch of test reults that are
related in groups, but aren't all in the same groups:

Test1-A
Salt 3
Chlorine 5

Test1-B
Iron 2
Calcium 4

Test2-A
VitC 1
Iron 3

So Test1-A and Test1-B are my related groups. My Sheet3 will (hopefully)
have:

Mineral Test1 Test2
Iron 2 3
VitC 1
Calcium 4
Chlorine 5
Salt 3


So I write Sheet3 as I get Tests from Sheet1, but I have to group them
(Test1's, regardless of the -[letter] all go together, and they aren't
necessarily in order, either. It's possible Sheet 1 will have a Test1-A,
then a Test2-A, then a Test1-B, and there could be any number of related
test groups (A, B, C, etc.).

My Sheet3 Starts with populating column A with all items tested, across
all groups. Then what I'm doing is using Find in Sheet1 to go down the
column that has the Test headers (Test1-A, Test1-B, etc.), and where there
is Test1, then I'm setting a range that includes the values underneath,
and then I to go into Sheet3, make my new Test1 column header, and find a
match in the 1st column of Sheet3 for the 1st column in my Sheet1's
temporary range , and put the related value in my Sheet3's Test1 column.
Then I Find the next Test header in Sheet1, go through THAT range, put the
values in Sheet3 under Test1, etc.

Right now I use Find in Sheet 1 to get my 1st Test Header, getting the
range, and then using Vlookup to find a match from my range in Sheet3's
Column A. Where I'm stuck is, since I'm not doing the Vlookup from
Sheet3, I can't figure out how to put my data in the right row where the
match was. What I have so far is:

For Each cell In MySheet1Range '(which I just made after finding "Test1")
If Not IsError(Application.VLookup(cell.Value,
SHeet3Column1Range, 1, False)) Then

Sheet3.Cells(cell.Row,
Range(MyTest1Column).Column).Value = Application.VLookup(cell.Value,
MySheet1Range, 3, False)
End If
Next cell

I get a 1004 error at the Sheet3.cells line.

1) Am I doing this in a really dumb way? I'm writing the Sheet3 as I go,
it's not already built, so that's why I'm not doing Vlookups from there;
When I go through Test Headers in Sheet1 with my Find, if there's a Test1,
then I make the Test1 column in Sheet3, and then I have to put the values
in from the (1 or several) Test1 groups in Sheet1

2) If I'm not doing it in a dumb way, I think part of my problem is that
I'm using the cell.row to get the row in Sheet3, but it's using the
cell.row from Sheet1, and I can't figure out how to get the right row the
match is in Sheet3

Thanks for any help on this. I've used Find a little bit, but can't
figure out how to apply it here instead of Vlookup.
I appreciate any help