• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lookup Data in 2 Sheets

cyliyu

Member
I have a set of Data in sheet "BList", with both S/No and Date code combined in one cell - a "/" in between them.
I want to check and compare them with sheet "AList" - S/No and Date Code in 2 separate cells.
and to show the result with Status not equal to "CAR" but Serial Number & Date Code found in BList.

Sample file is attached. Thanks.
 

Attachments

PCI

Member
See next code
Code:
Option Explicit

Sub Treat()
Const Ws1N = "AList"
Const Ws2N = "BList"
Const StaRef = "Car"
Const DispAdd = "P9"
Dim Dic   As Object
Set Dic = CreateObject("Scripting.Dictionary")
Dim LR  As Integer, I  As Integer, II  As Integer
Dim WkRg  As Range
Dim F, K, T

    With Sheets(Ws1N)
        LR = .Cells(Rows.Count, "B").End(3).Row
        For I = 12 To LR
            Dic.Item(.Cells(I, "C") & " / " & .Cells(I, "D")) = .Cells(I, "E")
        Next I
    End With

    With Sheets(Ws2N)
        LR = .Cells(Rows.Count, "C").End(3).Row
        Set WkRg = .Range(DispAdd).CurrentRegion
        WkRg.Offset(2, 0).ClearContents

        Set WkRg = .Cells(4, "C").CurrentRegion
        For Each K In Dic.keys
            If (Dic.Item(K) <> StaRef) Then
                Set F = WkRg.Find(What:=K, LookIn:=xlValues, LookAt:=xlWhole)
                If (Not F Is Nothing) Then
                    II = II + 1
                    With .Range(DispAdd)
                        .Offset(II, 0) = II
                        .Offset(II, 1) = .Parent.Cells(F.Row, "C")
                        .Offset(II, 2) = .Parent.Cells(4, F.Column)
                        .Offset(II, 3) = K
                        .Offset(II, 4) = Dic.Item(K)
                    End With
                End If
            End If
        Next K
    End With
End Sub
 
Last edited:

p45cal

Well-Known Member
In the attached, I've added conditional formatting to sheet BList in cells D5:K29 to colour the background pink.
Does that help? I haven't tried to fill the small table.

[The formula used in conditional formatting is also in cell V5, copied down and across. It's an array-entered formula.
But you do not need these formulae on the sheet, they're only there to show you how the conditional formatting formula was verified as working. You can delete them.]
 

Attachments

cyliyu

Member
Thanks, PCI.
I implement your code with some changes to my master worksheet and it works.
Appreciate your help.
 

cyliyu

Member
Thanks, P45Cal.
An alternate method without using VBA code and it works for me.
Appreciate your help too.
 
Top