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

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? — Dale M

daneshjai daneshjai · Accepted Answer · 2012-12-05T23:32:09

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

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

1 Answers