If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Function syntax to compare cell contents
I am trying to create a macro to compare the contents of cells in two
columns. I tried using the 'EXACT' syntax, but it doesn't seem to work for the comparison I need to do. Here is an example of the comparison I would like to perform: cell a: apple, orange cell b: apple orange = true cell a: apple, orange, pear cell b: apple, orange = true cell a: apple, orange cell b: apple, pear = false So the logic I am trying to capture is that if anything in cell b is foriegn to cell a than the result is false. But if cell b contains data that is found in cell a than the formula is true, even if cell a contains more data than cell b. My question is if it is possible to create a formula to solve this logic. Thank you, ES |
#2
|
|||
|
|||
Here's a shot
Sub Test() Dim iLastRow As Long Dim i As Long, j As Long, k As Long Dim fA As Boolean, fB As Boolean Dim aryB, aryA iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To iLastRow aryA = Split(Replace(Cells(i, "A").Value, ",", ""), " ") aryB = Split(Replace(Cells(i, "B").Value, ",", ""), " ") For j = LBound(aryA) To UBound(aryA) fA = False For k = LBound(aryB) To UBound(aryB) If aryA(j) = aryB(k) Then fA = True Exit For End If Next k If Not fA Then Exit For Next j For j = LBound(aryB) To UBound(aryB) fB = False For k = LBound(aryA) To UBound(aryA) If aryB(j) = aryA(k) Then fB = True Exit For End If Next k If Not fB Then Exit For Next j If fA Or fB Then Cells(i, "C").Value = True Else Cells(i, "C").Value = False End If Next i End Sub -- HTH Bob Phillips "ES" wrote in message ... I am trying to create a macro to compare the contents of cells in two columns. I tried using the 'EXACT' syntax, but it doesn't seem to work for the comparison I need to do. Here is an example of the comparison I would like to perform: cell a: apple, orange cell b: apple orange = true cell a: apple, orange, pear cell b: apple, orange = true cell a: apple, orange cell b: apple, pear = false So the logic I am trying to capture is that if anything in cell b is foriegn to cell a than the result is false. But if cell b contains data that is found in cell a than the formula is true, even if cell a contains more data than cell b. My question is if it is possible to create a formula to solve this logic. Thank you, ES |
#3
|
|||
|
|||
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Cannot assign Cell a value in module function | Nick | Worksheet Functions | 1 | October 5th, 2004 04:49 AM |
How to hide Hypyerlink cell contents ? | Wellie | General Discussion | 2 | October 2nd, 2004 03:30 PM |
Place The Contents Of A Cell From The Active Worksheet Into A Cell On An Inavtive Worksheet | Minitman | Worksheet Functions | 1 | February 25th, 2004 04:26 AM |
Do you have what it takes... | Frank Kabel | Worksheet Functions | 1 | February 22nd, 2004 08:30 PM |