Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

how to skip blank cells that match? 1

Status
Not open for further replies.

Xsi

Programmer
May 29, 2015
121
SE
Hello people I have made following code but I need help to skip the blank cells that matching,

Code:
Sub Compare()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim r As Long
    Dim m As Long
    Dim s As Long
    Dim n As Long
    
    
    Set ws1 = ActiveSheet
    m = ws1.range("A" & ws1.Rows.Count).End(xlUp).Row
    
    Set ws2 = Worksheets("sheet2")
    n = ws2.range("A" & ws2.Rows.Count).End(xlUp).Row
    
    For r = 1 To m
        For s = 1 To n
        
       
            If ws1.range("A" & r) = ws2.range("A" & s) And _
               ws2.range("B" & r) = ws2.range("B" & s) Then
                
                
                MsgBox ws2.range("A" & s).Text
                Exit For
            End If
        Next s
    Next r
End Sub

Could someone help me?

Thank you in advance


 
Code:
For s = 1 To n
    If Trim(ws1.Range("A" & r)) <> "" Or Trim(ws2.Range("B" & r)) <> "" Then
        If ws1.Range("A" & r) = ws2.Range("A" & s) And _
           ws2.Range("B" & r) = ws2.Range("B" & s) Then
            MsgBox ws2.Range("A" & s).Text
            Exit For
        End If
    End If
Next s

That OK?

"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.
 
Thank you very much awesome
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top