compare data in two sheets and write it in another sheet in same workbook

Question

What I want my code to do: there is a sheet called "DB" which has data taken from table. I have "XML" WorkSheet which has data taken from XML. I want to take a column name "FName" from "DB" sheet and search for the same column name "FName" in "XML" sheet. If column name matches, then take the corresponding value from the "DB" sheet and Compare with the "XML" Sheet. I have another sheet "results" in the same workbook. I have taken the column names from "DB" sheet and pasted transpose (column in row wise). I have to do below scenario:
1. if column names match in both the sheets:
- Search for Value match for the corresponding column name in both the sheets. If the value match, In "results" sheet I have to write "Match" or "NO Match" in the column next to the row which has the column name "FName".

2.if column names DOES NOT match in both the sheets:
- In "results" sheet I have to write "NO Matching COLUMN" in the column next to the row which has the column name "FName".

currently, in this code, I wanted to parse through each column in "DB" sheets and search for that column. But I am getting "Application-Defined or Object-Defined Error" .

Please let me know how to achieve the above scenarios:

Dim FindString As Range
Dim Rng As Range
Dim i, j As Integer
Dim finalcol As Long
Worksheets("DB").Select
finalcol = Worksheets("DB").Cells(1, Application.Columns.Count).End(x1toleft).column
On Error Resume Next
For i = 1 To finalcol
 FindString = Cells(1, i).Value
If Trim(FindString) <> "" Then
 With Sheets("xml").Range("A:A")
 Set Rng = .Find(What:=FindString, _
 After:=.Cells(.Cells.Count), _
 LookIn:=xlValues, _
 LookAt:=xlWhole, _
 SearchOrder:=xlByRows, _
 SearchDirection:=xlNext, _
 MatchCase:=False)
 If Not Rng Is Nothing Then
 Application.Goto Rng, True
 Else
 MsgBox "Nothing found"
 End If
 End With
End If
Next i
 On Error GoTo 0

End Sub

• Dale M: It seems that this could be more easily achieved and maintained by using excel formulas and avoiding vba altogether. I am not exactly sure what you are attempting though, can you put a sample of the DB data, the XML data and the Results?

Answer 1

From what you described, this is what I could come up with. The rows are dependent upon how many are in the "DB" sheet

 Private Function CheckColumn(SheetN As String, col As String) 
 'checks if a column is in sheet returns errorif not found 
 On Error GoTo NotHere
 CheckColumn = WorksheetFunction.Match(col, Sheets(SheetN).Rows(1), 0)
 On Error GoTo 0
Exit Function
 NotHere:
 MsgBox col & " was not found in sheet " & SheetN 
End Function
Sub MatchNoMatch()
Dim rngDB As Range
Dim colname As String
Dim sh1 As String
Dim sh2 As String
Dim sh3 As String
Dim dbcol As Integer
Dim xmlcol As Integer
Dim rcol As Integer
Dim Firstrow As Long
Dim Lastrow As Long
'column you want to search up
colname = "FNAME"
'the sheets you want to find the column in
sh1 = "DB"
sh2 = "XML"
sh3 = "RESULT"
'Gets the column number from each sheet
dbcol = CheckColumn(sh1, colname)
xmlcol = CheckColumn(sh2, colname)
rcol = CheckColumn(sh3, colname)
'Sets the range for sh1 to do compare
With Worksheets(sh1)
 Firstrow = .UsedRange.Cells(1).Row + 1
 Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
 Set rngDB = .Range(.Cells(Firstrow, dbcol), .Cells(Lastrow, dbcol))
End With
'checks each value against sh2 and writes in sh3. If more values in sh2 they will be ignored
For Each e In rngDB
 If e.Value <> Worksheets(sh2).Cells(e.Row, xmlcol) Then
 Worksheets(sh3).Cells(e.Row, rcol + 1) = "NO MATCH"
 Else
 Worksheets(sh3).Cells(e.Row, rcol + 1) = "MATCH"
 End If
Next e
End Sub

• tester: Hi @blueblook, I executed your code. In "results" sheet, I am getting MATCH or NO MATCH for more than 100000 times. I have the value '102469' under column "FNAME". Is it because of that? I tried changing the value to '3' under "FNAME", still it is displaying "MATCH" or NOMatch" in more than 100000 rows.
• daneshjai: I revised the code. This was due to the way Set rngDB was defined. Do you have unused rows in FName after your data stops? You can check this by simulating .End(xlDown) by pressing End+arrow[down]. The code will run for however many used rows there are in the DB sheet.