Using Find to Cut & Paste
 

I have an index spreadsheet that lists a code number for an item in one
column and the name of the item in a different column. I want to use this
information in different spreadsheets that have only the code numbers. Is it
possible to take a code number in one spreadsheet, search for it in the index
spreadsheet, copy the corresponding name, and paste the name back into the
spreadsheet that just has the codes?

Thanks!

Using Find to Cut & Paste
 

Sub getdata()

With Sheets("Summary")
RowCount = 1
Do While .Range("A" & RowCount) < ""
ID = .Range("A" & RowCount)
With Sheets("Data")
Set c = .Columns("A").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Did not find ID : " & ID)
Else
Data = c.Offset(rowoffset:=0, columnoffset:=1)
End If
End With
If Not c Is Nothing Then
.Range("B" & RowCount) = Data
End If
Loop

End With

End Sub


"Mark P" wrote:

I have an index spreadsheet that lists a code number for an item in one
column and the name of the item in a different column. I want to use this
information in different spreadsheets that have only the code numbers. Is it
possible to take a code number in one spreadsheet, search for it in the index
spreadsheet, copy the corresponding name, and paste the name back into the
spreadsheet that just has the codes?

Thanks!

Using Find to Cut & Paste
 

Assume the code number is in column A and the description
in column B of the Index sheet.
Assume the code number is in column A of all other sheets.


Sub getDescript()
Dim sh As Worksheet, lr As Long, srcRng As Range
lr = Sheets("Index").Cells(Rows.Count, 1).End(xlUp).Row
Set srcRng = Sheets("Index").Range("A2:A" & lr)
For Each sh In ThisWorkbook.Sheets
For Each c In sh.Range("A2:A" & _
Cells(Rows.Count, 1).End(xlUp).Row)
Set i = srcRng.Find(c.Value, LookIn:=xlValues)
If Not i Is Nothing Then
i.Offset(0, 1).Copy c.Offset(0, 1)
End If
Next
Next
End Sub


"Mark P" <Mark wrote in message
...
I have an index spreadsheet that lists a code number for an item in one
column and the name of the item in a different column. I want to use this
information in different spreadsheets that have only the code numbers. Is
it
possible to take a code number in one spreadsheet, search for it in the
index
spreadsheet, copy the corresponding name, and paste the name back into the
spreadsheet that just has the codes?

Thanks!

Using Find to Cut & Paste
 

Dear Mark

You can use a combination of INDEX and MATCH functions to achieve this.

Workbook INdex.xls Sheet1 contain index numbers and names in ColA and ColB
Current workbook Sheet1 Col A1 = number . Use this formula to retrieve the
name from Index.xls

=IF(ISNA(MATCH(A1,[INDEX]Sheet1!$A$1:$A$3,0))=TRUE,"",INDEX([Book2]Sheet1!$A$1:$B$3,MATCH(A1,[INDEX]Sheet1!$A$1:$A$3,0),2))

If this post helps click Yes
--------------
Jacob Skaria

"Mark P" wrote:

I have an index spreadsheet that lists a code number for an item in one
column and the name of the item in a different column. I want to use this
information in different spreadsheets that have only the code numbers. Is it
possible to take a code number in one spreadsheet, search for it in the index
spreadsheet, copy the corresponding name, and paste the name back into the
spreadsheet that just has the codes?

Thanks!

Using Find to Cut & Paste
 

That worked great, Jakob. Thanks!

"Jacob Skaria" wrote:

Dear Mark

You can use a combination of INDEX and MATCH functions to achieve this.

Workbook INdex.xls Sheet1 contain index numbers and names in ColA and ColB
Current workbook Sheet1 Col A1 = number . Use this formula to retrieve the
name from Index.xls

=IF(ISNA(MATCH(A1,[INDEX]Sheet1!$A$1:$A$3,0))=TRUE,"",INDEX([Book2]Sheet1!$A$1:$B$3,MATCH(A1,[INDEX]Sheet1!$A$1:$A$3,0),2))

If this post helps click Yes
--------------
Jacob Skaria

"Mark P" wrote:

I have an index spreadsheet that lists a code number for an item in one
column and the name of the item in a different column. I want to use this
information in different spreadsheets that have only the code numbers. Is it
possible to take a code number in one spreadsheet, search for it in the index
spreadsheet, copy the corresponding name, and paste the name back into the
spreadsheet that just has the codes?

Thanks!