Improve company productivity with a Business Account.Sign Up

Status: Solved
Priority: Medium
Security: Public
Views: 516
Last Modified: 2012-06-27

macro to find compare cells contents from one colume to another

I am trying to write a macro that examines if the cells of one column have unique values as compared to the cells of another column. So basically I want to look at cell F1, then compare it to every cell in Column G. If it finds a duplicate value anywhere in Column G it will put "duplicate" in Cell M1, if not it will put "unique". I have started the macro and have the following:

Sub Macro1()
Dim i As Integer, Finder As Variant, SearchRange As Range, c As Variant

i = 1
Cells(i, 5).Select

Do Until Selection.Value = ""
Finder = Right((Selection.Value), (Len(Selection.Value) - 7))
Set SearchRange = ActiveSheet.Columns(7)
For Each c In SearchRange
If c.Value = Finder Then
Selection.Offset(0, 7).Value = "duplicate"
Else: selections.Offset(0, 7).Value = "unique"
End If
Next
i = i + 1
Loop
End Sub

However I keep getting a runtime error 13 - type mismatch at 'If c.Value = Finder Then'

any help sorting me out would be much appreciated. thanks.

bpfsr

Asked: 2011-03-09

Advertise Here

1 Solution

LVL 10

peetmCommented: 2011-03-09

You can use something like typeof, e.g., If typeof Finder Is String Then ...

I assume that one of these (either side of the = ) is null perhaps - isn't there an IsNull() to test for that - if indeed it is the case?

LVL 30

SiddharthRoutCommented: 2011-03-09

You don't need to write a macro for this. Simply use the vlookup() :)

Sid

LVL 20

Ardhendu SarangiSr. Project ManagerCommented: 2011-03-09

Hi,

you are getting an error because your code - Len(Selection.Value) - 7 is returning negative values (value less than 0) which renders the right function as uncomputable.

Here's an easy way to do this.

Ardhendu

LVL 30

SiddharthRoutCommented: 2011-03-09

Put this formula in Cell M1 and pull it down.

=IF(ISERROR(VLOOKUP(F1,G:G,1,0)),"Unique","duplicate")

Sid

LVL 20

Ardhendu SarangiSr. Project ManagerCommented: 2011-03-09

sorry, here's the code.

Sub Macro1()
Dim strg As String
strg = Range("F1")
For i = 1 To Cells(65536, "G").End(xlUp).Row
If Range("G" & i) = strg Then
    Range("M1") = "Duplicate in cell " & Range("G" & i).Address
    Exit Sub
Else
    Range("M1") = "Unique"
End If
Next
End Sub

Open in new window

LVL 30

SiddharthRoutCommented: 2011-03-09

One more way which is faster than the above formula.

Put this in M1.

=IF(COUNTIF(G:G,F1)>0, "Duplicate","Unique")

Sid

LVL 30

SiddharthRoutCommented: 2011-03-09

Ardhendu: Didn't see this post as well.

Sid

bpfsrAuthor Commented: 2011-03-09

SiddharthRout - vlookup will not work as I am looking to compare the values of each cell in Column F against the values of each and every cell in column g

LVL 30

SiddharthRoutCommented: 2011-03-09

>>> compare the values of each cell in Column F against the values of each and every cell in column g

If I understand you correctly then it will :)

Sid

LVL 30

SiddharthRoutCommented: 2011-03-09

Sample attached.

Sid
sample.xlsx

LVL 30

SiddharthRoutCommented: 2011-03-09

Sorry the above example was for countif as I mentioned in ID: 35085987.

Here is the example for Vlookup. I would suggest Countif as compared to vlookup.

Sid
sample.xlsx

LVL 20

Ardhendu SarangiSr. Project ManagerCommented: 2011-03-09

I think both formulas and macros will get you the same results. can you please post a sample spreadsheet of your data?

bpfsrAuthor Commented: 2011-03-09

I can't figure out why SiddharthRout but neither of your solutions is working. What I was really hoping for was somebody who could correct my existing macro, rather than starting from scratch....

bpfsrAuthor Commented: 2011-03-09

pari123 - I have checked and re-checked, it is not returning a negative value. the value in the cell in question is a web adrress, the formula there is simply removing the http://

LVL 20

Ardhendu SarangiSr. Project ManagerCommented: 2011-03-09

if you can post a sample worksheet, then perhaps we can fix your macro. sorry but its a bit hard to guess the data here :)

LVL 30

SiddharthRoutCommented: 2011-03-09

>>>I can't figure out why SiddharthRout but neither of your solutions is working. What I was really hoping for was somebody who could correct my existing macro, rather than starting from scratch....

Can you upload a sample workbook?

Sid

bpfsrAuthor Commented: 2011-03-09

here the the sample
Scott-Project.xlsm

LVL 30

SiddharthRoutCommented: 2011-03-09

There is nothing in Col G? Do you want to compare it with Col A of Sheet Lookup?

Sid

LVL 20

Ardhendu SarangiSr. Project ManagerCommented: 2011-03-09

which of the columns are you trying to compare here? I am sorry but your columns in the question above don't match with your data.

LVL 30

SiddharthRoutCommented: 2011-03-09

If my understanding is correct then you are trying to match the base URLs. If this is true then try this macro.

Sub Find_Matches()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lastRowws1 As Long
    Dim TempSource As String
    Dim aCell As Range
    
    Set ws1 = Sheets("Master")
    Set ws2 = Sheets("Lookup")
    
    lastRowws1 = ws1.Range("F" & Rows.Count).End(xlUp).Row
    
    '~~> Loop through rows in Col F
    For i = 2 To lastRowws1
        TempSource = Replace(ws1.Range("F" & i).Value, "http://", "")
        pos = InStr(1, TempSource, "/")
        
        If pos <> 0 Then
            TempSource = Mid(TempSource, 1, pos - 1)
        End If
        
        '~~> The base URL in Lookup Sheet
        Set aCell = ws2.Columns(1).Find(What:=TempSource, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        
        '~~> Output the values in Col M
        If Not aCell Is Nothing Then
            ws1.Range("M" & i).Value = "Duplicate"
        Else
            ws1.Range("M" & i).Value = "Unique"
        End If
    Next i
End Sub