G
Guest
In VBA code I would like to check a colum of values for uniqueness.
Basically, I want to loop through the column and write duplicate instances to
an error file.
From the table example listed below:
Row Num column
1 123
2 124
3 123
4 3
5 123
6 456
7 456
8 sdfg
9 12345
10 sdfg
11 sdfg
My result set should look like:
Row Error Description
1 123 duplicate record
3 123 duplicate record
5 123 duplicate record
6 456 duplicate record
7 456 duplicate record
8 sdfg duplicate record
10 sdfg duplicate record
11 sdfg duplicate record
However, the result set I'm getting is only searching for the first value.
1 123 duplicate record
3 123 duplicate record
5 123 duplicate record
The code I'm using is listed below:
Public Sub a000000000000dupsearchrev()
Dim dupval As String
Dim curval As String
Dim rownum As String
Dim varMstrSrch()
Dim searchval As String
Dim i As Integer
Dim ubnd As Long
Sheets("master").Activate
varMstrSrch() = Range("b2:b17") '65536") ' Read it in
Range("b2").Select
Do Until IsEmpty(ActiveCell)
dupval = ActiveCell
searchval = dupval 'this is the value we are seeking
For i = LBound(varMstrSrch()) To UBound(varMstrSrch())
ubnd = UBound(varMstrSrch())
If varMstrSrch(i, 1) = searchval Then
curval = ActiveCell
rownum = ActiveCell.Offset(0, -1)
Sheets("DataEntry-Errors").Activate
ActiveCell = rownum
ActiveCell.Offset(0, 1) = curval
ActiveCell.Offset(0, 2) = "duplicate record"
ActiveCell.Offset(1, 0).Select
Sheets("master").Activate
End If
ActiveCell.Offset(1, 0).Select
Next i
'ActiveCell.Offset(1, 0).Select
Loop
End Sub
If I remove the comment I get duplicate search results. Any help anyone can
offer would be greatly appreciated. Thanks in advance.
AxLa
Basically, I want to loop through the column and write duplicate instances to
an error file.
From the table example listed below:
Row Num column
1 123
2 124
3 123
4 3
5 123
6 456
7 456
8 sdfg
9 12345
10 sdfg
11 sdfg
My result set should look like:
Row Error Description
1 123 duplicate record
3 123 duplicate record
5 123 duplicate record
6 456 duplicate record
7 456 duplicate record
8 sdfg duplicate record
10 sdfg duplicate record
11 sdfg duplicate record
However, the result set I'm getting is only searching for the first value.
1 123 duplicate record
3 123 duplicate record
5 123 duplicate record
The code I'm using is listed below:
Public Sub a000000000000dupsearchrev()
Dim dupval As String
Dim curval As String
Dim rownum As String
Dim varMstrSrch()
Dim searchval As String
Dim i As Integer
Dim ubnd As Long
Sheets("master").Activate
varMstrSrch() = Range("b2:b17") '65536") ' Read it in
Range("b2").Select
Do Until IsEmpty(ActiveCell)
dupval = ActiveCell
searchval = dupval 'this is the value we are seeking
For i = LBound(varMstrSrch()) To UBound(varMstrSrch())
ubnd = UBound(varMstrSrch())
If varMstrSrch(i, 1) = searchval Then
curval = ActiveCell
rownum = ActiveCell.Offset(0, -1)
Sheets("DataEntry-Errors").Activate
ActiveCell = rownum
ActiveCell.Offset(0, 1) = curval
ActiveCell.Offset(0, 2) = "duplicate record"
ActiveCell.Offset(1, 0).Select
Sheets("master").Activate
End If
ActiveCell.Offset(1, 0).Select
Next i
'ActiveCell.Offset(1, 0).Select
Loop
End Sub
If I remove the comment I get duplicate search results. Any help anyone can
offer would be greatly appreciated. Thanks in advance.
AxLa