Update column with values but exclude existing

[sharky12345]

I'm trying to update a column with values depending on a certain criteria using this;

    Sheet14.Range("N4:O23").ClearContents
    For Each cell In Sheet14.Range("B4:B23")
        If cell <> "" Then
            If cell.Offset(0, 2) = "" Then
            Else
                If cell.Offset(0, 2) = "Constant" Or cell.Offset(0, 2) = "Visit" Or cell.Offset(0, 2) = "Area" Or cell.Offset(0, 2) = "Site" Then
                    Lastrow = Sheet14.Range("N65536").End(xlUp).Row + 1
                    Sheet14.Range("N" & Lastrow).Value = cell.Offset(0, 15).Value
                    Sheet14.Range("O" & Lastrow).Value = cell.Offset(0, 2).Value
                End If
            End If
        End If
    Next

Column N, apart from being updated by this routine, can also be edited manually by the user.

I added the clearcontents part because I wanted to ensure that the routine didn't duplicate the values if the routine was run twice in error, but the problem is that if column N already has a value added manually the routine clears it, so what I need is a way to retain any manually added values and only update the column with the values found with the above criteria, but also ensuring that they are not duplicated if they already exist.

 

[Dryver14]

Not fully understanding this sharky

what exactly are you hoping to clear from column N if not manually input figures?

 

[sharky12345]

Morning, and thanks...

The code posted will loop through C4:C23 and if it finds one of the criteria it will update N with the value from B and O with the value from C.

Hopefully I still have you and have not lost you so far!

Because I've put the code on the worksheet_change event, if I try to add a manual entry in column N, it first clears the range and then updates the cells from the code, so any manual entry I've put in is cleared.

Does this make sense or have I confused you further?

 

[Dryver14]

seems to me you will end up with an endless loop!

is it not worth protecting column N so manual entries are not possible?

 

[sharky12345]

I can't protect column N unfortunately, I must allow manual entries so I'll have to think of another way....

 

[Dryver14]

the only thing I can think of if the manual entry is likely to be different than what is in B then perhaps the clear contents can work backwards on a loop

eg if N4 = B4 then clearcontents else

 

[sharky12345]

I'll give that a try and report back....didn't know a loop could go backwards!

 

[Dryver14]

I don't really mean loop backwards , I mean look backwards to check what is in N is what you want to clear or not

 

[sharky12345]

Got it - will give that a try.