VBA Check list and paste values not found in list

[phillip87]

Row 'I' is a dynamically changing column, Row 'H' looks to see if the value of column 'I' has already been listed in column 'N' and returns "Listed" or "Need To Add" which tells us to add it into column 'N' so the formulas can fill and we can review etc.

I am looking for a VBA to take away the manual copy and paste if column 'I' is not listed in column 'N'.

I get the this is a bit of a circular reference with 'I' checking 'N' and then pasting from 'I' into 'N'.

Correction H checks N and it pastes from I to N.

 

[HongRu]

Try.

Sub Add_N()
    Dim Rng_N As Range
    Set Rng_N = Range("N2") ' set the cell of the title of N categories
    
    Dim Rng_I As Range
    Set Rng_I = Range("I2") ' set the cell of the title of I categories
    
    Dim Data_N
    Data_N = Range(Rng_N.Offset(1), Range("N" & Rows.Count).End(xlUp))
    Dim Rows_N
    Rows_N = UBound(Data_N)
    
    Dim Data_I
    Data_I = Range(Rng_I.Offset(1), Range("I" & Rows.Count).End(xlUp))
    Dim Rows_I
    Rows_I = UBound(Data_I)
    
    Dim Checked As Boolean
    Dim i, j
    For i = 1 To Rows_I
        For j = 1 To Rows_N
            If Data_I(i, 1) = Data_N(j, 1) Then
                Checked = True
                Exit For
            End If
        Next j
        If Checked = False Then
            Range("N" & Rows.Count).End(xlUp).Offset(1) = Data_I(i, 1)
        End If
        Checked = False
    Next i
End Sub

 

[phillip87]

Try.

Sub Add_N()
    Dim Rng_N As Range
    Set Rng_N = Range("N2") ' set the cell of the title of N categories
   
    Dim Rng_I As Range
    Set Rng_I = Range("I2") ' set the cell of the title of I categories
   
    Dim Data_N
    Data_N = Range(Rng_N.Offset(1), Range("N" & Rows.Count).End(xlUp))
    Dim Rows_N
    Rows_N = UBound(Data_N)
   
    Dim Data_I
    Data_I = Range(Rng_I.Offset(1), Range("I" & Rows.Count).End(xlUp))
    Dim Rows_I
    Rows_I = UBound(Data_I)
   
    Dim Checked As Boolean
    Dim i, j
    For i = 1 To Rows_I
        For j = 1 To Rows_N
            If Data_I(i, 1) = Data_N(j, 1) Then
                Checked = True
                Exit For
            End If
        Next j
        If Checked = False Then
            Range("N" & Rows.Count).End(xlUp).Offset(1) = Data_I(i, 1)
        End If
        Checked = False
    Next i
End Sub

Thanks, it doesn't error but it also doesn't seem to do anything within the sheet where it should be pasting remains blank or empty. Also Range 'I' is a formula and I am needing it to just paste the values. I managed to get another code working but could not get it to just paste the values.

 

[phillip87]

I have found the below which works however I cannot get it to paste the value.

Sub AddCatCodesToList()

Dim statuscol As Range
Dim status As Range
Dim pastecell As Range


Set statuscol = Sheet1.Range("H3:H20")

For Each status In statuscol

If Sheet1.Range("N3") = "" Then
Set pastecell = Sheet1.Range("N3")
Else
Set pastecell = Sheet1.Range("N2").End(xlDown).Offset(1, 0)
End If

If status = "Need To Add" Then status.Offset(0, 1).Resize(1, 1).Copy pastecell

Next status

End Sub

 

[HongRu]

Thanks, it doesn't error but it also doesn't seem to do anything within the sheet where it should be pasting remains blank or empty. Also Range 'I' is a formula and I am needing it to just paste the values. I managed to get another code working but could not get it to just paste the values.

Do Column I and Column N belong to different Sheets?

 

[phillip87]

Do Column I and Column N belong to different Sheets?

No, Column N is a pasted value of column I. Column I is a dynamic list showing the info from the newest data upload, however we are wanting to update the data twice a day and compare the 2 uploads which is why column I is dynamic as it is only counting what has been loaded in that update. Column N shows the Categories from both updates and gives the respective count for comparison. The quantities values are pasted (values only) next to N before each fresh update so we can see an old v new count. I am just trying to find a way to stop them having to copy and paste any non listed categories across

 

[HongRu]

Thanks, it doesn't error but it also doesn't seem to do anything within the sheet where it should be pasting remains blank or empty. Also Range 'I' is a formula and I am needing it to just paste the values. I managed to get another code working but could not get it to just paste the values.

My code works well in my Excel.
It copys Column I's values to Column N if the values don't exist in N.

I don't know why it remains blank or empty in your Excel.

 

[Alex Blakenburg]

I have found the below which works however I cannot get it to paste the value.

Just change this line to what I have below:

        If status = "Need To Add" Then pastecell.Value = status.Offset(0, 1).Resize(1, 1).Value

PS: @HongRu's code worked me for me too.

 

[phillip87]

Just change this line to what I have below:

        If status = "Need To Add" Then pastecell.Value = status.Offset(0, 1).Resize(1, 1).Value

PS: @HongRu's code worked me for me too.

Thanks guys, I had made a work around but I will certainly use this when taking the next step in automating this to the final product. Thanks.

 

[Alex Blakenburg]

Thanks for letting us know. Glad you found something that worked for you.