Unexplained Error with code

[lucpian]

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

[MikeO]

I think I understand what you're trying to do. Try this for the function:

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 CheckCell As Range
Dim CheckRange As Range
Dim strVal As String

With ActiveSheet.Range("A1:IV1")
Set c = .Find(ColumnHeader, LookIn:=xlValues)
If Not c Is Nothing Then
Set CheckRange = Range(c.Offset(1), c.EntireColumn.Cells(65536).End(xlUp))
For Each CheckCell In CheckRange.Cells
If Application.CountIf(CheckRange, "=" & CheckCell.Value) > 1 Then
CheckCell.Interior.ColorIndex = 8
End If
Next CheckCell
Else
MsgBox "Header : - " & ColumnHeader & " - not found !", vbInformation
End If
End With

End Function


If you're getting the msgbox saying that the header was not found, that means no cells in the first row have the exact phrase "Bar Code" in it. Check for extra spaces/characters in the cell.

[Charlize]

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.

LucpianI guess the other thread is solved (The one for checking on the length of the values in a column with a specific header.)? Glad I could help you out.

Charlize