Using Find to Cut & Paste

[Mark P]

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!

 

[joel]

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

 

[JLGWhiz]

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

 

[Jacob Skaria]

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

 

[Mark P]

That worked great, Jakob. Thanks!

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

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!