lucpian
04-10-2008, 08:15 AM
Hi All,
Good morning. Please, I do have this code which is written to check for duplicates, but it keep coming up that the header is not seen. Here is the code.
Function CheckforDuplicateBarcodes(ColumnHeader As String)
Dim rowcount As Long
Dim String1 As String
Dim String2 As String
Dim R As Long
Dim c As Range
Dim strVal As String
'This is when they reposition the headers
'but still at row 1
With ActiveSheet.Range("A1:IV1")
Set c = .Find(ColumnHeader, LookIn:=xlValues)
If Not c Is Nothing Then
'This is adapted to count the no of rows in active column
rowcount = ActiveSheet.Range(Split(c.Address, "$")(1) & "65536").End(xlUp).Row
For R = 2 To rowcount
strVal = LTrim(RTrim(ActiveSheet.Cells(R, c.Column).Value))
'MsgBox Len(strVal)
ActiveSheet.Cells(R, c.Column).Value = strVal
'With Sheet1
'rowcount = .Cells(.Rows.Count, ColumnHeader).End(xlUp).Row
'For R = 2 To rowcount
If Application.CountIf.ActiveSheet.Cells(R, c.Column).Value > 1 Then
ActiveSheet.Cells(R, c.Column).Interior.ColorIndex = 8
End If
'Next R
'End With
Next R
Else
MsgBox "Header : - " & ColumnHeader & " - not found !", vbInformation
End If
End With
End Function
Sub Duplibutton()
CheckforDuplicateBarcodes "Bar Code"
End Sub
Please, I need your help, and thanks Charlize for your help.
Lucpian
Good morning. Please, I do have this code which is written to check for duplicates, but it keep coming up that the header is not seen. Here is the code.
Function CheckforDuplicateBarcodes(ColumnHeader As String)
Dim rowcount As Long
Dim String1 As String
Dim String2 As String
Dim R As Long
Dim c As Range
Dim strVal As String
'This is when they reposition the headers
'but still at row 1
With ActiveSheet.Range("A1:IV1")
Set c = .Find(ColumnHeader, LookIn:=xlValues)
If Not c Is Nothing Then
'This is adapted to count the no of rows in active column
rowcount = ActiveSheet.Range(Split(c.Address, "$")(1) & "65536").End(xlUp).Row
For R = 2 To rowcount
strVal = LTrim(RTrim(ActiveSheet.Cells(R, c.Column).Value))
'MsgBox Len(strVal)
ActiveSheet.Cells(R, c.Column).Value = strVal
'With Sheet1
'rowcount = .Cells(.Rows.Count, ColumnHeader).End(xlUp).Row
'For R = 2 To rowcount
If Application.CountIf.ActiveSheet.Cells(R, c.Column).Value > 1 Then
ActiveSheet.Cells(R, c.Column).Interior.ColorIndex = 8
End If
'Next R
'End With
Next R
Else
MsgBox "Header : - " & ColumnHeader & " - not found !", vbInformation
End If
End With
End Function
Sub Duplibutton()
CheckforDuplicateBarcodes "Bar Code"
End Sub
Please, I need your help, and thanks Charlize for your help.
Lucpian