C
CompleteNewb
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
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