Check if data in Sheet1 matches with Sheet2 and then update sheet1

[aman1234]

Hi All

I got stuck in a problem. Please see attached the workbook. There is data present in Sheet1 and Sheet2.I want to check the following things:

1. For each record in Sheet1, If "F" column stores "list work" then don't do anything and move to step 2.

2. Check for each record, If data in "F" column of Sheet1 matches with "H" column data of sheet2 then store the value of corresponding "E" column of sheet2 in "G" column of sheet1.

3. If it doesn't match then leave that record and move to next record of sheet2.
I tried writing the following code but it doesn't work:

 Dim cn As ADODB.Connection

  Dim rs As ADODB.Recordset

  Set cn = New ADODB.Connection

  cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _

        "Data Source=J:\ABC.mdb;"

 

  Set rs = New ADODB.Recordset

 Dim wb As Workbook

 Dim ws As Worksheet

 Set wb = ActiveWorkbook

     Dim i As Integer

     Dim j As Integer

     Dim strsql As String

 

Set ws = ThisWorkbook.Worksheets("sheet1")

Set ws1 = ThisWorkbook.Worksheets("sheet2")

For i = 2 To 282

For j = 2 To 135

If ws.Cells("F", i).Value = "list work" Then

Exit For

Else

If ws.Cells("F", i).Value = ws1.Cells("H", j) Then

ws.Cells("G", i).Value = ws1.Cells("H", j)

Exit For

Else

Next j

End If

Next j

Next i

Can anyone please help me in this?

Thanks

[aman1234]

Hi Guys
I am getting type mismatch error at the red line in the following code:

Private Sub Workbook_Open()
  Dim cn As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim r As Long
    ' connect to the Access database
  Set cn = New ADODB.Connection
  cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=J:\WilliamsLea-AIMM\Unit Rate MI\Alice's Adventures in Wonderland\The Mad Hatter\WorkQueue.mdb;"
        
 Set rs = New ADODB.Recordset
 Dim wb As Workbook
 Dim ws As Worksheet
 Set wb = ActiveWorkbook

     Dim i As Integer
     Dim j As Integer
     Dim strsql As String
     
Set ws = ThisWorkbook.Worksheets("sheet1")
Set ws1 = ThisWorkbook.Worksheets("sheet2")

For i = 2 To 282
For j = 2 To 135
If ws.Cells("F", i).Value = "list work" Then
Exit For
ElseIf ws.Cells("F", i).Value = ws1.Cells("H", j) Then
ws.Cells("G", i).Value = ws1.Cells("H", j)
Exit For
End If
Next j
Next i
End Sub

[aman1234]

sorted guys. I was writing rows n columns values other way around. Thanks anyway.