Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that change words in predefined numbers
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 :Dayton 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 ? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that change words in predefined numbers
Try
=INDEX(D:D,MATCH(B1,C:C,0)) -- __________________________________ HTH Bob "andrei" wrote in message ... 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 :Dayton 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 ? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that change words in predefined numbers
Works only for the first cell ( I have "4" in cell E1) . For the rest of them
i get error "Bob Phillips" wrote: Try =INDEX(D:D,MATCH(B1,C:C,0)) -- __________________________________ HTH Bob "andrei" wrote in message ... 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 :Dayton 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 ? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that change words in predefined numbers
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 "andrei" wrote in message ... 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 :Dayton 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 ? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that change words in predefined numbers
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" wrote in message ... 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 :Dayton 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 ? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that change words in predefined numbers
Thanks guys !
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that change words in predefined numbers
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" wrote: 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" wrote in message ... 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 :Dayton 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 ? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that change words in predefined numbers
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" wrote in message ... 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" wrote: 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" wrote in message ... 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 :Dayton 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 ? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that change words in predefined numbers
The problem was leading spaces . =TRIM(B1) solved the problem
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I change the numbers in words. ( Ex.100.00 = One Hundred) | New Users to Excel | |||
Need macro that transforms key words into predefined numbers | Excel Programming | |||
Change Month (words) to (numbers) | Excel Worksheet Functions | |||
How can I change Numbers in WOrds in Excel | Excel Discussion (Misc queries) | |||
how to change numbers into words, | Excel Discussion (Misc queries) |