Alright everyone, I know very very little about programing a function i
Excel.
Now that we have that out of the way this is what I'm looking at.

My boss gave me two excel sheets one is 626 rows and the other is 1300
rows and 256 columns. My job is to take a gene ID(listed in documen
1), search for it in document two, then once it is found it needs t
identify the cell in column A of what ever row it was found in an
paste it back in document 2.

Document 1's set up isn't important, but document 2 is setup as such:

Every cell in column A is the name that I need pasted, and in the res
of the cells in that row is where I will find the gene ID from documen
1. Let me write an example.

___A________B______C_______D_______
TC38123 CB61619 CD78923 DG909288
TC90123 CD99203 JR18239 JX920347

And document 2 continues on in this manner for a very long time.

So lets say I'm searching for CD78923 it will be found in C1, then
will look in column A of that same row to find the ID TC38123, then
paste that back in document 2. That is what I need a formula for an
so I ask you all for your help, because if I can't get a formula then
have to do it all by hand...:eek

--
Tar
-----------------------------------------------------------------------
Taru's Profile: http://www.excelforum.com/member.php...fo&userid=3549
View this thread: http://www.excelforum.com/showthread.php?threadid=55266

You say formula, but you posted in programming. So perhaps by formula you
meant code:

Dim rng as Range, cell as Range, rng1 as Range
With Worksheets('Sheet1")
set rng = .Range("A1:A626")
End with
With Worksheets("Sheet2')
for each cell in rng
set rng1 = .Range("B1:Z13000").Find(cell.Value)
if not rng1 is nothing then
cell.offset(0,1).Value = .Cells(rng1.row,1)
end if
Next
end With

--
Regards,
Tom Ogilvy


"Taru" wrote:


Alright everyone, I know very very little about programing a function in
Excel.
Now that we have that out of the way this is what I'm looking at.

My boss gave me two excel sheets one is 626 rows and the other is 13000
rows and 256 columns. My job is to take a gene ID(listed in document
1), search for it in document two, then once it is found it needs to
identify the cell in column A of what ever row it was found in and
paste it back in document 2.

Document 1's set up isn't important, but document 2 is setup as such:

Every cell in column A is the name that I need pasted, and in the rest
of the cells in that row is where I will find the gene ID from document
1. Let me write an example.

___A________B______C_______D_______
TC38123 CB61619 CD78923 DG909288
TC90123 CD99203 JR18239 JX920347

And document 2 continues on in this manner for a very long time.

So lets say I'm searching for CD78923 it will be found in C1, then I
will look in column A of that same row to find the ID TC38123, then I
paste that back in document 2. That is what I need a formula for and
so I ask you all for your help, because if I can't get a formula then I
have to do it all by hand...


--
Taru
------------------------------------------------------------------------
Taru's Profile: http://www.excelforum.com/member.php...o&userid=35494
View this thread: http://www.excelforum.com/showthread...hreadid=552664

I thank you for your imput, but I really don't know anything about
excel. So alot of that doesn't mean much to me. Could you simplify it
perhaps?? thank you


--
Taru
------------------------------------------------------------------------
Taru's Profile: http://www.excelforum.com/member.php...o&userid=35494
View this thread: http://www.excelforum.com/showthread...hreadid=552664

OK I read a little and learned very very little about VBA.

I opened VBA and added a Module, I actually copied and pasted exactly
what you wrote into the module and ran it just like it is below.

Dim rng as Range, cell as Range, rng1 as Range
With Worksheets('Sheet1")
set rng = .Range("A1:A626")
End with
With Worksheets("Sheet2')
for each cell in rng
set rng1 = .Range("B1:Z13000").Find(cell.Value)
if not rng1 is nothing then
cell.offset(0,1).Value = .Cells(rng1.row,1)
end if
Next
end With

After running this a got an error that said Compile error;
Expected:expression.
I assumed this was because you have an apostrophy(') before sheet1
instead of a quote ("). I replaced the ' with a " and that problem went
away. I also modified the ranges so that they fit my document, i.e., I
changed set rng to .Range("F2:F262") and rng1 to .Range("B1:IV13627")

I ran it again and got this error, Compile Error; Invalid outside
procedure. And it highlights with worksheets("sheet1"). and Sheet1 is
the actual name of the sheet I'm using. I went ahead and changed the
second sheets name to its real name, VVGI.TC_EST.

However, I'm not sure If I'm clear on what I need it to do. I don't
need it to compare an entire column. I need it to find a specific cell
and report back the first cell in that row.


--
Taru
------------------------------------------------------------------------
Taru's Profile: http://www.excelforum.com/member.php...o&userid=35494
View this thread: http://www.excelforum.com/showthread...hreadid=552664

Sub FindCells()
Dim rng As Range, cell As Range, rng1 As Range
With Worksheets("Sheet1")
Set rng = .Range("F2:F262")
End With
With Worksheets("VVGI.TC_EST")
For Each cell In rng
Set rng1 = .Range("B1:IV13627").Find(cell.Value)
If Not rng1 Is Nothing Then
cell.Offset(0, 1).Value = .Cells(rng1.Row, 1)
End If
Next
End With
End Sub

Tested in xl2003

As written, it searches VVGI.TC_EST
for each cell in Sheet1!F2:F262. If a match is found, it returns the value
from column A of VVGI.TC_EST in the same row to the adjacent cell being
searched for. For example, if searching for the value in F5, the results are
placed in G5

If you only want to search for a single value in one cell, change
Set rng = .Range("F2:F262")
to something like (searching for value in F5 only:)

Set rng = .Range("F5")

if you wanted the results in column A of that row on Sheet1, then change

cell.Offset(0, 1).Value = .Cells(rng1.Row, 1) ' on cell to the left
to
cell.Offset(0, -5).Value = .Cells(rng1.Row, 1) ' 5 cells to the right - col A

--
Regards,
Tom Ogilvy





"Taru" wrote:


OK I read a little and learned very very little about VBA.

I opened VBA and added a Module, I actually copied and pasted exactly
what you wrote into the module and ran it just like it is below.

Dim rng as Range, cell as Range, rng1 as Range
With Worksheets('Sheet1")
set rng = .Range("A1:A626")
End with
With Worksheets("Sheet2')
for each cell in rng
set rng1 = .Range("B1:Z13000").Find(cell.Value)
if not rng1 is nothing then
cell.offset(0,1).Value = .Cells(rng1.row,1)
end if
Next
end With

After running this a got an error that said Compile error;
Expected:expression.
I assumed this was because you have an apostrophy(') before sheet1
instead of a quote ("). I replaced the ' with a " and that problem went
away. I also modified the ranges so that they fit my document, i.e., I
changed set rng to .Range("F2:F262") and rng1 to .Range("B1:IV13627")

I ran it again and got this error, Compile Error; Invalid outside
procedure. And it highlights with worksheets("sheet1"). and Sheet1 is
the actual name of the sheet I'm using. I went ahead and changed the
second sheets name to its real name, VVGI.TC_EST.

However, I'm not sure If I'm clear on what I need it to do. I don't
need it to compare an entire column. I need it to find a specific cell
and report back the first cell in that row.


--
Taru
------------------------------------------------------------------------
Taru's Profile: http://www.excelforum.com/member.php...o&userid=35494
View this thread: http://www.excelforum.com/showthread...hreadid=552664

thank you very much, this is exactly what I need. However I still hav
a few questions.

first off I'm supposed to enter your code as a module, right?

Second can I activate that module anywhere in the sheet, and I would d
so by typing =module1

If it is supposed to be a module, and I can type =module anywhere i
the sheet to activate it then I have one more questions

When I run the module excel gives me an error of #Name which it call
an invalid name error

--
Tar
-----------------------------------------------------------------------
Taru's Profile: http://www.excelforum.com/member.php...fo&userid=3549
View this thread: http://www.excelforum.com/showthread.php?threadid=55266