Excel VBA script to compare data in two differen columns and populate in new Spreadsheet

[raghz]

Dear,

I am new to VBA scripting , my requirement is
code in Excel vba to find out any present in "File Name" another column present from Sheet1 having keywords(Keywords is the column name) and the value that are not in sheet2, . Results should be output in new excel file.
Sheet 1
[TABLE="width: 1397"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]File name[/TD]
[TD]Keywords[/TD]
[/TR]
[TR]
[TD]Xyz.pdf
[/TD]
[TD]Test
[/TD]
[/TR]
</tbody>[/TABLE]
Sheet 2
[TABLE="width: 329"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Keywords[/TD]
[/TR]
[TR]
[TD]Test
[/TD]
[/TR]
[TR]

[/TR]
[TR]
[TD]Test1
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sub ColumnCompare()

Dim arange As Range, brange As Range, crange As Range

Dim aws As Worksheet, bws As Worksheet

Dim WrdArray() As String

Dim objExcel As Object, objWb As Workbook
Dim ws As Worksheet
Dim rData As Range

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

Set objWb = objExcel.Workbooks.Add
Set ws = objWb.Worksheets("Result")



Set aws = ThisWorkbook.Worksheets("sheet1")

Set bws = ThisWorkbook.Worksheets("sheet2")

lastAcol = aws.Cells(1, Columns.Count).End(xlToLeft).Column

lastBcol = bws.Cells(1, Columns.Count).End(xlToLeft).Column

Set arange = aws.Range("A1").Resize(, lastAcol).Find("Keywords", LookAt:=xlWhole)
Set crange = aws.Range("B1").Resize(, lastAcol).Find("File Name", LookAt:=xlWhole)

If arange Is Nothing Then

MsgBox "Column name from Sheet A was not found.  Please restart the macro and enter a different column name."

Exit Sub

End If

Set brange = bws.Range("A1").Resize(, lastBcol).Find("Keywords", LookAt:=xlWhole)

If brange Is Nothing Then

MsgBox "Column name from Sheet B was not found.  Please restart the macro and enter a different column name."

Exit Sub

End If

lastBrow = bws.Cells(Rows.Count, brange.Column).End(xlUp).Row
Dim j As Integer
j = 1
    For i = 1 To aws.Cells(Rows.Count, arange.Column).End(xlUp).Row - 1

               If brange.Resize(lastBrow, 1).Find(Split(arange.Offset(i, 0).Value, ",")) Is Nothing Then
           
                 crange.Offset(i, 0).Interior.Color = 65535
                 ws.Rows(j).Copy crange.Offset(i, 0).Value
                 j = j + 1
                
               End If
    Next i

objWb.SaveAs ("C:\dev\Example.xlsx")
objWb.Close
objExcel.ActiveWorkbook.Close
objExcel.ActiveWorkbook.Quit
objExcel.Quit
Set objExcel = Nothing
End Sub

Thanks in advance for the help

 

[raghz]

An update the Values in Sheet1 i.e the keywords are also with multiple keywords like

Test,test1 that is the reason I am using a split function.