Comparing two lists

[everscern]

hi all. One more question till the end of my project. so your response is very much appreciated.

See cell B36. The number 6 should be 20. but the macro compares it with the above circled data. How do i modify it?

Thanks so much.

Right now my code is like this:

Private Sub SMIDialog_Button18_Click()
Dim txt As String, r As Range

   If WorksheetFunction.CountA(Range("b31:b35")) <> 5 Then Exit Sub
   Range("b36") = Empty
   txt = Range("b31").Text & "_" & Range("b32").Text & "_" & Range("b33").Text & "_" & Range("b34").Text & "_" & Range("b35").Text
   For Each r In Range(("S31:T36"), Cells(2, Columns.Count).End(xlToLeft))
      If txt = r.Text & "_" & r.Offset(1).Text & "_" & r.Offset(2).Text & "_" & r.Offset(3).Text & "_" & r.Offset(4).Text Then
         Range("b36").Value = r.Offset(5).Value
         Exit For
      End If
   Next

End Sub

 

[acw]

Hi

Try changing

For Each r In Range(("S31:T36"), Cells(2, Columns.Count).End(xlToLeft))

to

For Each r In Range(("S31:T36"), Cells(31, Columns.Count).End(xlToLeft))


Tony

 

[everscern]

wow. thanks!