Matching cells from 2 files

[fkheng]

I have 2 files with data. What i want to do is to match rows of data, and if a match is found, i want to update information from 1 file to the other...

to do this, means for each row in the 1st file, i will have to do a search in the whole 2nd file...

is this possible?

[WorkHorse]

Do you just need to match the values in one column to the values in the other book? This example matches values in col A in one book to values in col A of another book and if found copies over the data from the found row.

Sub ReplaceRowData()

Dim strFindWhat As String
Dim rngFound As Range
Dim i As Long

' Loop rows in Book 1.
For i = 1 To Workbooks("Book1").Sheets("Sheet1").UsedRange.Rows.Count

' Get text from col A of Book 1.
strFindWhat = Workbooks("Book1").Sheets("Sheet1").Range("A" & i).Value

' Find in col A of Book 2.
Set rngFound = Workbooks("Book2").Sheets("Sheet1").Cells.Find( _
What:=strFindWhat, LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)

' If found, replace values in Book 1 with values from found row in Book 2.
If Not rngFound Is Nothing Then
Workbooks("Book1").Sheets("Sheet1").Rows(i).Value = _
Workbooks("Book2").Sheets("Sheet1").Rows(rngFound.Row).Value
End If

Next i

End Sub :)

[fkheng]

thank you for your reply

well, basically, bboth files have identical fields...

the fields i am mainly concerned with in this situation is :
1) Name
2) Gender field

Currently, the 2nd file has the correct gender fields

I want to match the names in both files, once a match is found, the gender field is copied from the 2nd file to overwrite the gender field in the 1st file. Also, it would be nice to be able to highlight the cells of those names in the 1st file had no match in the 2nd file.

Can your code do this?

[WorkHorse]

Sure.

Sub ReplaceGender()

Dim strFindWhat As String
Dim rngFound As Range
Dim strColWithName As String
Dim strColWithGender As String
Dim i As Long

strColWithName = "A"
strColWithGender = "B"

' Loop rows in Book 1.
For i = 1 To Workbooks("Book1").Sheets("Sheet1").UsedRange.Rows.Count

' Get text from col A of Book 1.
strFindWhat = Workbooks("Book1").Sheets("Sheet1").Range(strColWithName & i).Value

' Find in col A of Book 2.
Set rngFound = Workbooks("Book2").Sheets("Sheet1").Columns(strColWithName & ":" & strColWithName).Find( _
What:=strFindWhat, LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)

' If found, replace values in Book 1 with values from found row in Book 2.
If Not rngFound Is Nothing Then
Workbooks("Book1").Sheets("Sheet1").Range(strColWithGender & i).Value = _
Workbooks("Book2").Sheets("Sheet1").Range(strColWithGender & rngFound.Row).Value
Else
' Highlight row with no match.
Workbooks("Book1").Sheets("Sheet1").Rows(i).Interior.ColorIndex = 37
End If

Next i

End Sub :)

[fkheng]

Workbooks("Book1").Sheets("Sheet1").UsedRange.Rows.Count

Workbooks("Book1").Sheets("Sheet1").Range(strColWithName & i).Value

these 2 statements when i used, gave error mesages "Subscript out of range", how can i fix this?

also, i have to put my excel file called "Book1" in the same folder as that of my vb project with this code right?

[WorkHorse]

The subsripts are then workbook and worksheet names that you are passing. Sochange "Book1" and/or "Sheet1" to match the names of the actual workbook & worksheet that you are referenceing.

[fkheng]

i did save my file as Book1 and the default name for sheet is sheet1, id idn't change that...
so it should be same rite?

so wat's the cause ut hink?

[fkheng]

elo, sorry for long w ait....i've tried but i dunno y i still get the error message despite doing wat u've siad...

[WorkHorse]

The you must be setting strColWithName to a column outside of the used range. What line is rasiing the error?

[fkheng]

For i = 1 To Workbooks("Book1").Sheets("Sheet1").UsedRange.Rows.Count

this line gives the error

subscript out of range


i checked, the StrColWithName is correct, both are correct...
should i attach a sample of my code and files for u to check?

[WorkHorse]

Yep. Post the files. Either the workbook or worksheet name doesn't match your code.

[fkheng]

okay, i still can't figure out, but here are my sample files...

[WorkHorse]

You need to use .xls in the book names: "Book1.xls". Either that or use the Workbook indexes: Workbook(1), Workbook(2), etc. :)

[fkheng]

i did wat u said but i still get the errros...attached are the files...

[fkheng]

er.........could u help me see if there's any problem with it please as i can't find the problem...please advise...thank you.

[WorkHorse]

You didn't say you were doing this for a VB app. You can't just copy in VBA code. ou have to create an application object and open the workbooks. Here:

Const xlValues = -4163
Const xlWhole = 1
Const xlByRows = 1
Const xlNext = 1

Sub ReplaceGender()

Dim strFindWhat As String
Dim strColWithName As String
Dim strColWithGender As String
Dim strFilePath As String
Dim appExcel As Object
Dim rngFound As Object
Dim i As Long

strColWithName = "A"
strColWithGender = "B"
strFilePath = "C:\Junk\Excel\Excel2\" ' <---Set the path to the workbooks here.

Set appExcel = CreateObject("Excel.Application")

With appExcel

' Open workbooks.
.Workbooks.Open (strFilePath & "Book1.xls")
.Workbooks.Open (strFilePath & "Book2.xls")

' Loop rows in Book 1.
For i = 1 To .Workbooks("Book1.xls").Sheets("Sheet1").UsedRange.Rows.Count

' Get text from col A of Book 1.
strFindWhat = .Workbooks("Book1.xls").Sheets("Sheet1").Range(strColWithName & i).Value

' Find in col A of Book 2.
Set rngFound = .Workbooks("Book2.xls").Sheets("Sheet1").Columns(strColWithName & ":" & strColWithName).Find( _
What:=strFindWhat, LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)

' If found, replace values in Book 1 with values from found row in Book 2.
If Not rngFound Is Nothing Then
.Workbooks("Book1.xls").Sheets("Sheet1").Range(strColWithGender & i).Value = _
.Workbooks("Book2.xls").Sheets("Sheet1").Range(strColWithGender & rngFound.Row).Value
Else
' Highlight row with no match.
.Workbooks("Book1.xls").Sheets("Sheet1").Rows(i).Interior.ColorIndex = 37
End If

Next i

.Workbooks("Book1.xls").Save
.Workbooks("Book1.xls").Close False
.Workbooks("Book2.xls").Close False

.Quit

End With

Set appExcel = Nothing

End Sub

Private Sub Command1_Click()
ReplaceGender
End Sub :)

[fkheng]

hey there WorkHorse, really need to thank you for the help with the code...for your information it was a success!
although the whole search and replace operation took about 9 hours to complete (yeap! i left the PC running from morning to evening at my workplace), coz both of the files had over 7000 entries...

i need your advice on my post at http://www.vbforums.com/showthread.php?s=&threadid=268301

could you give me ur opinions there? thanx a lot!