Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.
Solved
macro to find compare cells contents from one colume to another
Posted on 2011-03-09
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.
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
11
5-
5
- +1
22 Comments
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?
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?
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
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
Put this formula in Cell M1 and pull it down.
=IF(ISERROR(VLOOKUP(F1,G:G
Sid
=IF(ISERROR(VLOOKUP(F1,G:G
Sid
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
One more way which is faster than the above formula.
Put this in M1.
=IF(COUNTIF(G:G,F1)>0, "Duplicate","Unique")
Sid
Put this in M1.
=IF(COUNTIF(G:G,F1)>0, "Duplicate","Unique")
Sid
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
>>> 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
If I understand you correctly then it will :)
Sid
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
Here is the example for Vlookup. I would suggest Countif as compared to vlookup.
Sid
sample.xlsx
I think both formulas and macros will get you the same results. can you please post a sample spreadsheet of your data?
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....
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://
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 :)
>>>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
Can you upload a sample workbook?
Sid
here the the sample
Scott-Project.xlsm
Scott-Project.xlsm
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.
If my understanding is correct then you are trying to match the base URLs. If this is true then try this macro.
Sid
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
Sid
Featured Post
This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Suggested Courses
Course of the Month9 days, 11 hours left to enroll
722 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.