vzachin
02-16-2009, 01:51 PM
hi,
i have the following code from Krishna Kumar in January 2008 which compared data in 2 columns (B & F) and placed the no matches in column M.
how can i modify this code to include the data from column e and place that in column L?
i will have about 5000 rows in each column. i suppose i can do a v-lookup to grab the data.
Sub kTest()
Dim a, i As Long, j As Long, w(), b, x
Dim Rng1 As Range, Rng2 As Range, r As Long
Set Rng2 = Range("b5", Range("b" & Rows.Count).End(xlUp))
Set Rng1 = Range("f5", Range("f" & Rows.Count).End(xlUp))
a = Rng1.Value
b = Rng2.Value
r = Application.Max(UBound(a, 1), UBound(b, 1))
ReDim w(1 To r, 1 To 1)
For i = 1 To UBound(a, 1)
If Not IsEmpty(a(i, 1)) Then
x = Application.Match(a(i, 1), b, 0)
If IsError(x) Then j = j + 1: w(j, 1) = a(i, 1)
End If
Next
If j > 0 Then Range("m5").Resize(j).Value = w
End Sub
thanks
zach
i have the following code from Krishna Kumar in January 2008 which compared data in 2 columns (B & F) and placed the no matches in column M.
how can i modify this code to include the data from column e and place that in column L?
i will have about 5000 rows in each column. i suppose i can do a v-lookup to grab the data.
Sub kTest()
Dim a, i As Long, j As Long, w(), b, x
Dim Rng1 As Range, Rng2 As Range, r As Long
Set Rng2 = Range("b5", Range("b" & Rows.Count).End(xlUp))
Set Rng1 = Range("f5", Range("f" & Rows.Count).End(xlUp))
a = Rng1.Value
b = Rng2.Value
r = Application.Max(UBound(a, 1), UBound(b, 1))
ReDim w(1 To r, 1 To 1)
For i = 1 To UBound(a, 1)
If Not IsEmpty(a(i, 1)) Then
x = Application.Match(a(i, 1), b, 0)
If IsError(x) Then j = j + 1: w(j, 1) = a(i, 1)
End If
Next
If j > 0 Then Range("m5").Resize(j).Value = w
End Sub
thanks
zach