Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and Paste Program
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and Paste Program
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and Paste Program
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and Paste Program
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and Paste Program
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and Paste Program
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and Paste Program
Is the name of the function you gave going to be findcells as in I should type in =findcells to activate it? Because this gives me a error, won't let me enter it. -- Taru ------------------------------------------------------------------------ Taru's Profile: http://www.excelforum.com/member.php...o&userid=35494 View this thread: http://www.excelforum.com/showthread...hreadid=552664 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and Paste Program
No partially. You are correct that you would put it in a module. the same
type as you would get doing Insert=Module I gave you a sub which would be run manually. Sounds like you want a user defined function (UDF) that you can use in the worksheet. Usage: =FindCell(F2,VVGI.TC_EST!$B$1:$IV$13275) Public Function FindCell(cell as Range, rng1 as Range) Dim rng2 as Range Set rng2 = rng1.Find(cell.Value) If Not rng2 Is Nothing Then FindCell = rng1.Parent.Cells(rng2.row,1).Value else FindCell = "Not found" End If End Sub Just not the FIND will not work in a UDF in versions of xl before xl2002. So it works in xl2002/XP and xl2003 -- Regards, Tom Ogilvy "Taru" wrote: thank you very much, this is exactly what I need. However I still have 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 do so by typing =module1 If it is supposed to be a module, and I can type =module anywhere in 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 calls an invalid name error. -- Taru ------------------------------------------------------------------------ Taru's Profile: http://www.excelforum.com/member.php...o&userid=35494 View this thread: http://www.excelforum.com/showthread...hreadid=552664 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and Paste Program
Hmmmmm, this is very difficult. If the sub must be ran "manually" then how do I activate it? is there a button? do I use the =FindCell(F2,VVGI.TC_EST!$B$1:$IV$13275) to activate the sub of the UDF? Either way I'm still getting an invalid name error everytime I run it. I ran the sub, I ran the UDF, and I can a combination(don't know what I was thinking) of the sub and UDF. and still get an invalid name error. I open the VB from the macros and then insert a module, I copy and paste the code you wrote for me then I close and return to document. Then I enter =findcells or =module1 or =FindCell(F2,VVGI.TC_EST!$B$1:$IV$13275) and I always get #NAME which means invalid name error. I checked the name of both sheets and they are all spelled correctly. I don't know whats wrong and it is so close.... -- Taru ------------------------------------------------------------------------ Taru's Profile: http://www.excelforum.com/member.php...o&userid=35494 View this thread: http://www.excelforum.com/showthread...hreadid=552664 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and Paste Program
Never mind the last post, I'm just dumb. But I figured it out and the sub worked great! thank you so much!:) -- Taru ------------------------------------------------------------------------ Taru's Profile: http://www.excelforum.com/member.php...o&userid=35494 View this thread: http://www.excelforum.com/showthread...hreadid=552664 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I paste worksheet to an other program through a HYPERLINK | Excel Discussion (Misc queries) | |||
How can I paste a worksheet to an other program through a HYPERLIN | Excel Worksheet Functions | |||
copy paste program | Excel Programming | |||
Paste cell content to another program | Excel Programming | |||
Copy/Paste program error | Excel Programming |