Comparing two lists

everscern

Board Regular
Joined
Oct 10, 2006
Messages
56


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:

Code:
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
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,671
Messages
5,549,346
Members
410,910
Latest member
DessertDiva
Top