Compare a column in two sheets and add value where its missing

Question

I am looking to compare column A from sheet("RETRTnew") to column A to sheet("RT"). I would like the code to do the check and if a value from RETRTnew is missing from RT then i would like a row to be added and copy the data from row above and paste as formulas. Then add the missing value to column A only. I am using the following code but it gives the

"block variable not set" error

at

Set xCopy = .Range(c, c.Offset(0, 1))

Sub ExportData()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False

Set rng_ID = Sheets("RT").Range("A96:A5000") 'set ID range on sheet2
Set Rng = Sheets("RETRTnew").Range("A2", Sheets("RETRTnew").Range("A2").End(xlDown)) 'set ID range on sheet1

With Sheets("RT")
For Each Cell In Rng 'loop to each cell in ID range on sheet1
Set c = rng_ID.Find(Cell.Value, lookat:=xlWhole) 'find if the cell value is in ID range on sheet2
    If c Is Nothing Then 'if not found
    Set xCopy = .Range(c, c.Offset(0, 1))
    c.Offset(1, 0).EntireRow.Insert 'insert entire row below the found cell
    c.Offset(0, 0).EntireRow.Copy
    c.Offset(1, 0).EntireRow.PasteSpecial Paste:=xlPasteFormulas
    xCopy.Copy Destination:=c.Offset(1, 0) 'copy the range above then paste
    

    Else 'if  found
        End If

Next Cell

End With


End Sub

After Suggestion from BigBen, I changed the code to now add the missing cell value to the end of the dataset in sheet RT. However, i would like to bring the formulas from row above, butwhen trying to copy paste the row above, for some reason is it not copying pasting the formulas. What could be the reason behind this?

For Each Cell In Rng 'loop to each cell in ID range on sheet1
oCost = Cell.Offset(0, 8).Value 'set the cost value
Set c = rng_ID.Find(Cell.Value, lookat:=xlWhole) 'find if the cell value is in ID range on sheet2
    If Not c Is Nothing Then 'if found
    
    Else 'if not found
    Set Ofill = Sheets("RT").Range("A5000").End(xlUp).Offset(1, 0) 'set the last blank cell in column B sheet2
    Ofill.Offset(1, 0).EntireRow.Insert
    Ofill.Offset(1, 0).EntireRow.Copy
    Ofill.Offset(1, 0).EntireRow.PasteSpecial Paste:=xlPasteFormulas
    Ofill.Value = Cell.Value 'fill the ID
        End If

You can't use `c`, because it is `Nothing`. You've already established that it's nothing in `If c Is Nothing Then`. — BigBen, Jun 30 '20 at 19:47
The only question then is where you want the row to be inserted. How should that be determined? — BigBen, Jun 30 '20 at 19:54
```If c is nothing``` was written to see if the value from sheet RETRTnew was found or not found within sheet RT. The row could be added to the end of a table in sheet "RT". As this value is sorted AtoZ, i can simply add a sort function at the end of the code to make sure everything appears in order. @BigBen — David D, Jun 30 '20 at 19:56
Then perhaps [find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) and copy everything to the row right below it? — BigBen, Jun 30 '20 at 19:57
I added the code based on your suggestion. This will bring in the value and paste at the end of the dataset. but before i bring in the new value to sheet "RT", i would like to copy paste a row in the last blank cell as pastespecials to bring in formulas only (within RT) and THEN replace the missing value in column A. The edit above does everything except bringing in the formulas. would you know why that is? @BigBen — David D, Jun 30 '20 at 22:44

score 1 · Answer 1 · answered Jun 30 '20 at 23:06

The following worked for me:


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False

Set rng_ID = Sheets("RT").Range("A96:A5000") 'set ID range on sheet2
Set Rng = Sheets("RETRTnew").Range("A2", Sheets("RETRTnew").Range("A2").End(xlDown)) 'set ID range on sheet1

With Sheets("RT")
For Each Cell In Rng 'loop to each cell in ID range on sheet1
oCost = Cell.Offset(0, 8).Value 'set the cost value
Set c = rng_ID.Find(Cell.Value, lookat:=xlWhole) 'find if the cell value is in ID range on sheet2
    If Not c Is Nothing Then 'if found
    
    Else 'if not found
    Set Ofill = Sheets("RT").Range("A5000").End(xlUp).Offset(1, 0) 'set the last blank cell in column B sheet2
    Ofill.Offset(1, 0).EntireRow.Insert
    Ofill.Offset(1, 0).EntireRow.Copy
    Ofill.Offset(0, 0).EntireRow.PasteSpecial Paste:=xlPasteFormulas
    Ofill.Value = Cell.Value 'fill the ID
        End If

Next Cell

End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Compare a column in two sheets and add value where its missing

1 Answers1