Hi.
I would like to compare two columns in Excel. Columns are as follows:Column A = xyz,123456,abc Column B = 123456
I use the following macro to compare columns where the data in both columns is identical. Can anyone advise how to adapt code when the data is as above?
Sub Find_Matches()
Dim CompareRange As Variant, x As Variant, y As Variant
Set CompareRange = Range("C72:C102")
' Loop through each cell in the selection and compare it to
' each cell in CompareRange.For Each x In Selection
For Each y In CompareRange
If x = y Then x.Offset(0, -1) = "match"
Next y
Next xEnd Sub
Thanks in advance for your help.
Hi, The following code uses the same ranges for data as your original post. The data to be tested is the selected range and it is tested against the data in the range C72 to C102
You will see that I have been a bit pedantic in some of the code - for example specifically using Value, although it may be the default for a range object.
Also I specifically DIM ranges as Range - this makes the IntelliSense work when creating or modifying code.
If VBA knows the variable's type, it can list available functions for it.For example change y to Variant, then go to y.Text
Delete the period and then insert it - nothing happens.
Do the same with y as Range and you will get Text as an immediate option.Sub Find_Matches() Dim CompareRange As Range Dim x As Range Dim y As Range Set CompareRange = Range("C72:C102") ' Loop through each cell in the selection and ' see if cells contain text in the cells in the CompareRange. For Each x In Selection For Each y In CompareRange If InStr(1, x.Text, y.Text) <> 0 And y.Value <> "" Then x.Offset(0, -1).Value = "match" End If Next y Next x End SubRegards
Hi Humar The code work perfectly and is saving loads of time. Thank you so much.
You're welcome, Regards
Humar