Macro that change words in predefined numbers

[andrei]

Could not say better in subject but this is what i need :

In column A i have book titles , in column B i have their book authors , in
column C i have a lot of authors ( all authors in column B are to be found in
column C ) and in column D i have for each author a code . Something like
that :


A1 : A Fiery Peace in a Cold War
A2 : The National Parks: America's Best Idea
A3 : Dancing in the Dark: A Cultural History of the Great Depression

B1 :Neil Sheehan
B2 ayton Duncan
B3 :Morris Dickstein

C1 : John Keegan
C2 : Morris Dickstein
C3 : Rick Bragg
C4: Neil Sheehan
C5: Dayton Duncan

D1 : 1
D2 : 2
D3 : 3
D4 : 4
D5 : 5

In column E i need the code which corespondes to the author of the book in A
column . So , in A1 i have a book written by "Neil Sheehan" , so in E1 i
need the code "4" , because the author "Neil Sheehan" has this code

Can this be done ?

 

[Bob Phillips]

Try

=INDEX(D,MATCH(B1,C:C,0))

 

[andrei]

Works only for the first cell ( I have "4" in cell E1) . For the rest of them
i get error

 

[Don Guillett]

A simple vlookup entered in e1 and copied down.

=VLOOKUP(B1,$C$1:$D$5,2,0)
4

 

[JLGWhiz]

If you want to use code:

Sub authorcd()
Dim lr As Long, rng As Range, sh As Worksheet
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 2).End(xlUp).Row
Set rng = sh.Range("B2:B" & lr)
For Each c In rng
Set cd = sh.Range("C2", sh.Cells(Rows.Count, 3) _
.End(xlUp)).Find(c.Value, LookIn:=xlValues)
If Not cd Is Nothing Then
c.Offset(, 2).Copy c.Offset(, 3)
End If
Next
End Sub

 

[JLGWhiz]

Had typos in the other one, use this one:

Sub authorcd()
Dim lr As Long, rng As Range, sh As Worksheet
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 2).End(xlUp).Row
Set rng = sh.Range("B2:B" & lr)
For Each c In rng
Set cd = sh.Range("C2", sh.Cells(Rows.Count, 3) _
.End(xlUp)).Find(c.Value, LookIn:=xlValues)
If Not cd Is Nothing Then
cd.Offset(, 1).Copy c.Offset(, 3)
End If
Next
End Sub

 

[andrei]

Thanks guys !

 

[andrei]

I found a problem and can't figure out

I used in A column 38 titles , in column B their authors . In column C I put
all the authors - 44096 authors ( i know , a lot of them ) and in column D
their codes . I used the macro and surprise , from the 38 authors in B column
, it puts the codes only for 6 of them

I gave a quick Ctrl C followed by a CTRL+F for all authors from B column the
macro didn't put the code and I found they are in column C . So they should
receive the code in E column , but it doesn't happen

 

[JLGWhiz]

Could be a number of reasons. Difference in spelling is the primary one.
Should not be affected by leading spaces, but could be a difference in upper
and lower case The Find statement can be adjusted for the Case problem.
Just add in a comma and:

MatchCase:=xlFalse

after the LookIn:=xlValues

 

[andrei]

The problem was leading spaces . =TRIM(B1) solved the problem