CODEWITHSUNDEEP
excelvbacompare
David D
David D

Reputation: 31

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

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

Upvotes: 0

Views: 92

Answers (1)

David D
David D

Reputation: 31

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

Upvotes: 1

Related Questions