Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
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
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210 (2 * 3 * 5 * 7) or 2310 (2 * 3 * 5 * 7 * 11).
The larger templa…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month4 days, 11 hours left to enroll
636 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.