VBA macro not doing what I want

[Bonbi456]

Hi people,
I've been figthing with my vba script for a while now and there are two or three things that don't work and I'm about to give up.
Essentially, I want my macro to do two main things:
1. Concatenate the columns a, b and c, stick it in my table and check ONLY this column to see if anything appears two times (duplicates if you will). If it spots a duplicate, I want it to add an x to my error column (Column K)
2. I want the code to go through the rows and check a few conditions, these conditions are the following: G =< C-B, G=<E, H=<F. If these conditions are not respected, once again I want the program to put an X in the K column.
Right now, when I run it, Every single rows in the k column have an X, despite the data respecting every condition. When I remove the part of the code that deals with conditions (to test the duplicate thing), there are no rows that have an X in the K column, despite the data in the D column (The concanated column) being a duplicate.
I've tried changing a bunch of things a bunch of times but nothing works the way I want it to. In regards to the positionning of the columns and resetting the code everytime I run it so there isn't a bunch of error columns and concanated columns, it works. The parts where I'm having problems is the signaling in the errors column.
Any help is appreciated, sorry my code is a mess right now.

Also this is meant to operate on .csv files, I know the macro will not save on it but for what it's meant it's ok

Sub ConcatenateColumnsAndHighlightDuplicates()
    Dim lastRow As Long
    Dim concatRange As Range
    Dim lastCol As Long
    Dim dataRange As Range
    Dim cell As Range
    Dim dict As Object
    Dim lastColumn As Long
    Dim lastColumn1 As Long
    Dim i As Long
    Dim ws As Worksheet
   
    Set ws = ActiveSheet

lastCol = Cells(1, Columns.Count).End(xlToLeft).Column

    'Delete "Hole_ID/From/To" column if it exists
    For Each cell In Range("A1:Z1")
       If cell.Value = "Hole_ID/From/To" Then
       cell.EntireColumn.Delete
       End If
    Next
   
    For Each cell In Range("A1:Z1")
       If cell.Value = "Error" Then
       cell.EntireColumn.Delete
       End If
    Next
   
    ws.Cells(1, 10).Value = "Error"
   
    'Get last row of data
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row

    'Insert empty column before the concatenation range and name it "Hole_ID/From/To"
    Range("D1").EntireColumn.Insert Shift:=xlToRight
    Range("D1").Value = "Hole_ID/From/To"

    'Set range for concatenation
    Set concatRange = Range("A1:C" & lastRow)

    'Concatenate values and insert new column
    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    concatRange.Columns(4).Value = "=CONCATENATE(A1,""/"",B1,""/"",C1)"
    concatRange.Columns(4).Copy
    concatRange.Columns(4).PasteSpecial xlPasteValues

'Set up a dictionary to store unique values
Set dict = CreateObject("Scripting.Dictionary")

'Loop through column D and highlight duplicates
Set dataRange = Range("D1:D" & lastRow)
    For Each cell In dataRange
        key = Cells(cell.Row, "D").Value
        If dict.exists(key) Then
            Range(cell.Address).Cells(1, 11).Value = X
            Range(dict(key)).Cells(1, 11).Value = X
        Else
            dict.Add key, cell.Address
        End If
    Next cell

    For i = 2 To lastRow
        ' Check each condition
        If ws.Cells(i, "G").Value <= (ws.Cells(i, "C").Value - ws.Cells(i, "B").Value) _
            Or ws.Cells(i, "G").Value <= ws.Cells(i, "E").Value _
            Or ws.Cells(i, "H").Value <= ws.Cells(i, "F").Value Then
            ' Add an "X" to the last column of the current row
            ws.Cells(i, 10).Value = "X"
        End If
    Next i

End Sub

 

[Bonbi456]

Also this is meant to operate on .csv files, I know the macro will not save on it but for what it's meant it's ok

 

[Alex Blakenburg]

You are mixing and matching how you are referencing column K. You dictionary dupliate check loop is trying to put the X in column "N", also your Xs need quotation marks around them.

Try replacing your dictionary loop with this:

    For Each cell In dataRange
        Key = Cells(cell.Row, "D").Value
        If dict.exists(Key) Then
            Cells(cell.Row, "K").Value = "X"                    ' XXX Changed cell reference & Added Quote marks
            Cells(dict(Key), "K").Value = "X"                   ' XXX Changed cell reference & Added Quote marks
        Else
            dict.Add Key, cell.Row                              ' XXX Only need row
        End If
    Next cell

In terms of your conditions to add an X. Do you required only one of the conditions to be True or ALL the conditions to be True.
If it is ALL then change you ORs to ANDs.
If you don't want AND then we need an XL2BB with some sample data and an indication of which lines are being marked with X and shouldn't be.