newbie123
01-20-2006, 06:48 PM
hi,
i'm very new to vba and am completely stuck on how to do something.
basically, one workbook1 contains a worksheet with a column of first names
(Column B), last names (Column C), and cities (Column D). another workbook2 contains a worksheet with a specific city info, age info, gender, first and last names matching specific people from the other workbook... what i want to do is, look through workbook1 for everybody matching the city of workbook2 and from there, compare to see if the people in workbook1 can be found in workbook2. if they can be found, copy the info from workbook2 relating to the person found and paste that info into specific columns in workbook1. it seems like such an easy concept but its not working right at all for me!
here is a sample of my code:
Worksheets("People").Activate
Worksheets("People").Range("D1").Select
Dim count As Integer
Dim lastname As String
Dim firstname As String
Dim startrow As Integer
count = 1
'loop to go through all rows in workbook1 worksheet
Do While Not IsEmpty(ActiveCell)
'check to see if city in column D equals cities in workbook2
'note: previous to this, i have an inputbox in order to find out
'what city the other workbook2 is referring to
If Worksheets("People").Columns("D").Find(city,_
LookIn:=xlValues, lookat:=xlWhole).Row Then
'if city is found in workbook1, copy lastname and first name
lastname = Range("C" & count).Value
firstname = Range("B" & count).Value
'open up workbook2
Workbooks.Open Filename:=FName(n)
Worksheets(1).Activate
'search through all of workbook2 to see if the last name can be found
Do While (Worksheets(1).Columns("A").Find(lastname, _
LookIn:=xlValues, lookat:=xlWhole).Row)
'record the row that lastname is found in, if found
startrow = Worksheets(1).Columns("A").Find(lastname, _
LookIn:=xlValues, lookat:=xlWhole).Row
If Not startrow = 0 Then
'check to see if first name matches row with last name
If (Worksheets(1).Columns("A").Find(firstname, _
LookIn:=xlValues, lookat:=xlWhole).Row) Then
'if last name and first name match, copy info from 'G:J of same row containing last name
Range("G" & startrow & ":J" & startrow).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWorkbook.Close True
Sheets("People").Activate
'Paste the info copied previously into workbook1 that 'relates to the first and last name of the person searched
Range("E" & count).Select
ActiveSheet.Paste
End If
End If
Loop
End If
count = count + 1
ActiveCell.Offset(1, 0).Select
'loop to continue comparing people in both workbooks to
'copy necessary info
Loop
here is some pseudo code i was trying to follow:
InputBox: Enter City = CityInputted
On People.xls:
For all Cities in Column D that equal CityInputted
Search Last name in City.xls
If found, check if first name matches;
if first and last name matches: copy data from columns
G,H,I,J row (whatever last name is in) into People.xls where First and Last
name matches
if not found, look at all same last names until found and if
person is not found at all, go back to People.xls to search up the next
person containing city name
this doesnt seem to work for me at all.... i dont know why.... if anybody
can offer any advice or help that would be really appreciated. thank you
very much in advance.
i have also included 2 workbooks:
if you look at the two workbooks provided, basically what happens is
Workbook1.xls contains the macros to be run. From there, there is a menu
to import data from another workbook (Workbook2.xls). When importing, an
inputbox asks you to specify the city the imported workbook (Workbook2.xls)
belongs to. Then, in Workbook1.xls it searches through it to see if the
inputted city is found. If so, it copies the last name and first name and
stores it in a variable. Then, it goes back to the Workbook2.xls and goes
through each row to see if the last name can be found. if last name can be
found, it checks to see if the first name matches. if it does, it copies
the age,gender, year of birth information and then it pastes this
information into the related last name and first name in Workbook1.xls
sorry this post is so long :S i tried to be as detailed as possible...
i'm very new to vba and am completely stuck on how to do something.
basically, one workbook1 contains a worksheet with a column of first names
(Column B), last names (Column C), and cities (Column D). another workbook2 contains a worksheet with a specific city info, age info, gender, first and last names matching specific people from the other workbook... what i want to do is, look through workbook1 for everybody matching the city of workbook2 and from there, compare to see if the people in workbook1 can be found in workbook2. if they can be found, copy the info from workbook2 relating to the person found and paste that info into specific columns in workbook1. it seems like such an easy concept but its not working right at all for me!
here is a sample of my code:
Worksheets("People").Activate
Worksheets("People").Range("D1").Select
Dim count As Integer
Dim lastname As String
Dim firstname As String
Dim startrow As Integer
count = 1
'loop to go through all rows in workbook1 worksheet
Do While Not IsEmpty(ActiveCell)
'check to see if city in column D equals cities in workbook2
'note: previous to this, i have an inputbox in order to find out
'what city the other workbook2 is referring to
If Worksheets("People").Columns("D").Find(city,_
LookIn:=xlValues, lookat:=xlWhole).Row Then
'if city is found in workbook1, copy lastname and first name
lastname = Range("C" & count).Value
firstname = Range("B" & count).Value
'open up workbook2
Workbooks.Open Filename:=FName(n)
Worksheets(1).Activate
'search through all of workbook2 to see if the last name can be found
Do While (Worksheets(1).Columns("A").Find(lastname, _
LookIn:=xlValues, lookat:=xlWhole).Row)
'record the row that lastname is found in, if found
startrow = Worksheets(1).Columns("A").Find(lastname, _
LookIn:=xlValues, lookat:=xlWhole).Row
If Not startrow = 0 Then
'check to see if first name matches row with last name
If (Worksheets(1).Columns("A").Find(firstname, _
LookIn:=xlValues, lookat:=xlWhole).Row) Then
'if last name and first name match, copy info from 'G:J of same row containing last name
Range("G" & startrow & ":J" & startrow).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWorkbook.Close True
Sheets("People").Activate
'Paste the info copied previously into workbook1 that 'relates to the first and last name of the person searched
Range("E" & count).Select
ActiveSheet.Paste
End If
End If
Loop
End If
count = count + 1
ActiveCell.Offset(1, 0).Select
'loop to continue comparing people in both workbooks to
'copy necessary info
Loop
here is some pseudo code i was trying to follow:
InputBox: Enter City = CityInputted
On People.xls:
For all Cities in Column D that equal CityInputted
Search Last name in City.xls
If found, check if first name matches;
if first and last name matches: copy data from columns
G,H,I,J row (whatever last name is in) into People.xls where First and Last
name matches
if not found, look at all same last names until found and if
person is not found at all, go back to People.xls to search up the next
person containing city name
this doesnt seem to work for me at all.... i dont know why.... if anybody
can offer any advice or help that would be really appreciated. thank you
very much in advance.
i have also included 2 workbooks:
if you look at the two workbooks provided, basically what happens is
Workbook1.xls contains the macros to be run. From there, there is a menu
to import data from another workbook (Workbook2.xls). When importing, an
inputbox asks you to specify the city the imported workbook (Workbook2.xls)
belongs to. Then, in Workbook1.xls it searches through it to see if the
inputted city is found. If so, it copies the last name and first name and
stores it in a variable. Then, it goes back to the Workbook2.xls and goes
through each row to see if the last name can be found. if last name can be
found, it checks to see if the first name matches. if it does, it copies
the age,gender, year of birth information and then it pastes this
information into the related last name and first name in Workbook1.xls
sorry this post is so long :S i tried to be as detailed as possible...