Compare Rows from one Sheet then Change / Update / Add on another Sheet - is this possible?

[jonsharman]

Hi

I would like to see if something is possible before venturing into too much detail and if anyone can give me a steer on how this might work?

I currently have an Order / Profit Workbook that updates the Master Stock file at either the click of a button or when it is more than 24 hours out of date. The stock file is exported by a proprietary account system overnight into *.xlsx format and when the VBA code is triggered by either of the conditions above the macro copies the proprietary stock export sheet into the main workbook, deletes all the data from the Stock sheet within the Order / Profit workbook, copies the whole new stock from the export file onto the Stock sheet and then deletes the sheet that is no longer required.

The issue I have here is that if an item of stock becomes obsolete or goes out of stock it is deleted from the system overnight! When the routine runs it therefore deletes any trace of this item as having been in stock which causes a problem when you recall a quote in the Workbook as the row that contains the data in the Stock sheet has now been deleted. My first thought was just to keep adding the stock to the bottom of the Stock sheet but this of course results in duplicate stock records and doesn't allow for changes in the cost price, discount codes, retail price etc. meaning I end up having the same item in stock several times but with inconsistent pricing data so the quotes become inaccurate.

Is it possible to:

• Copy in the 'Import' sheet into the Workbook (this bit already works)
• Compare the 'Import' against the 'Stock' sheet
• If the unique identifier in Column G exists in both the 'Import' and 'Stock' sheets then check the data for that row in columns A - D and J - V and, if the data in that row in the 'Stock' sheet is inconsistent with the data in the 'Import' sheet then update 'Stock' sheet from the 'Import' sheet.
• If the unique identifier in Column G exists in the 'Import' sheet but not the 'Stock' sheet then add the data to the next clear row at the bottom of the 'Stock' sheet
• If the unique identifier in Column G exists in both the 'Import' and 'Stock' sheets and the data in all the columns for that row are consistent then do nothing.

If anyone has any guidance or can offer some code for me to start with as this one has got me stumped I would be very grateful!

Thanks

 

[JLGWhiz]

Most things are possible, some are impractical, but still possible. The code below would do the search and update you describe.

Sub update2()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, fn As Range, i As Long
Set sh1 = Sheets(1) 'This would be the Master Stock
Set sh2 = Sheets(2) 'This would be the import sheet
    For Each c In sh2.Range("G2", sh2.Cells(Rows.Count, 7).End(xlUp))
        Set fn = sh1.Range("G:G").Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                With sh2
                    For i = 1 To 22
                        If i < 5 Or i > 9 Then
                            If .Cells(c.Row, i).Value <> sh1.Cells(fn.Row, i).Value Then
                              sh1.Cells(fn.Row, i).Value = .Cells(c.Row, i)
                            End If
                        End If
                    Next
                End With
            End If
    Next
End Sub

Some modification would be required to fit your specific conditions.

 

[jonsharman]

Hi,

Thanks for the steer - very helpful!

The code works perfectly for modifying stock that already exists so I have now added a routine to add any stock that does not exist:

Sub update2()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, fn As Range, i As Long
Set sh1 = Sheets("Stock") 'This would be the Master Stock
Set sh2 = Sheets("Import") 'This would be the import sheet
    
    For Each c In sh2.Range("G2", sh2.Cells(Rows.Count, 7).End(xlUp))
        Set fn = sh1.Range("G:G").Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                With sh2
                    For i = 1 To 22
                        If i < 5 Or i > 9 Then
                            If .Cells(c.Row, i).Value <> sh1.Cells(fn.Row, i).Value Then
                              sh1.Cells(fn.Row, i).Value = .Cells(c.Row, i)
                            End If
                        End If
                    Next
                End With
            End If
    Next
    
    For Each c In sh2.Range("G2", sh2.Cells(Rows.Count, 7).End(xlUp))
        Set fn = sh1.Range("G:G").Find(c.Value, , xlValues, xlWhole)
            If fn Is Nothing Then
                With sh2
                    For i = 1 To 22
                            sh1.Cells(c.Row, i).Value = sh2.Cells(fn.Row, i).Value
                    Next i
                End With
            End If
    Next


End Sub

However this results in an error on this line - 'Object variable With block not set':

sh1.Cells(c.Row, i).Value = sh2.Cells(fn.Row, i).Value

What am I missing as I have tried several attempts to correct it?

Many Thanks

J

 

[JLGWhiz]

I thought you already had a procedure for adding new items. The prolem with the code you added is that the variable fn cannot be used since there is nothing to match in the other sheet.

 

[jonsharman]

Hi

Thanks for your help!
The way I have been updating the stock is rudimentary at the moment; the code below illustrates the current procedure. Essentially, and for ease, all I am doing is clearing the existing ‘Stock’ sheet and overwriting it with the data from the ‘Used VSB’ sheet. The problem here is that if an item has been removed from the accounting system then it no longer appears on the exported ‘Used VSB’ sheet and so becomes deleted from the ‘Stock’ sheet when this is run.
There are numerous sheets within the workbook that rely on Column G from the ‘Stock’ sheet to maintain the integrity of the quote data; if I delete all the stock data and overwrite it then any quotes that were prepared before the item goes out of stock cannot be recalled without an error. Likewise if I keep adding fresh stock to the bottom of the ‘Stock’ sheet then the unique identifier isn’t unique any more as it will end up adding the same stock reference each time the procedure is run.

What I need to do is compare the ‘Used VSB’ and ‘Stock’ sheets and a) update the data of the stock that already exists without adding it 2^nd, 3^rd, 4^th time etc. and then b) add any stock that doesn’t exist in the ‘Stock’ sheet from the ‘Used VSB’ sheet so the historical data is preserved.

Does that make any more sense? :S

Private Sub ImportData()
 
Dim ws As Worksheet
Dim wb1 As Workbook, wb2
Dim nextrow As Long
Dim sheetarray As Variant
Dim ans1 As Integer, ans2, ans3
Set wb1 = ActiveWorkbook
  
On Error GoTo ErrorHandler_ImportData
    
    ans1 = MsgBox("Would you like to update the Stock File now?", vbYesNo + vbQuestion, "Update Stock?")
   
    If ans1 = vbYes Then
   
    Application.ScreenUpdating = False
       
    FileToOpen = Sheets("FileSetup").Range("STOCKFILE")
 
    If FileToOpen = 0 Then
   
        MsgBox "No File Specified!", vbCritical & vbInformation, "WARNING: No File Found!"
   
    Exit Sub
 
    Else
      
    Set wb2 = Workbooks.Open(Filename:=FileToOpen)
    For Each Sheet In wb2.Sheets
        If Sheet.Visible = True Then
            Sheet.Copy After:=wb1.Sheets(wb1.Sheets.Count)
        End If
    Next Sheet
   
    wb2.Close
 
    End If
 
    Sheets("Stock").Rows("2:" & Rows.Count).ClearContents
 
    With Sheets("UsedVSB_MA5 (VS)")
   
       nextrow = .Range("B" & Rows.Count).End(xlUp).Row
        .Range("A2:Z" & nextrow).Copy
        Sheets("Stock").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
       
    End With
   
    Sheets("Stock").Activate
    Range("AA2") = "=TODAY()-I2"
    Range("AA2").AutoFill Destination:=Range("AA2:AA" & Cells(Rows.Count, "A").End(xlUp).Row)
    Range("AB2") = "=ROUNDDOWN(YEARFRAC(H2, TODAY(),1),0)"
    Range("AB2").AutoFill Destination:=Range("AB2:AB" & Cells(Rows.Count, "A").End(xlUp).Row)
    Range("AC2") = "=IF(V2=0,SRCNA,INDEX(SRCDESC,MATCH(V2,SRCCODE,0)))"
    Range("AC2").AutoFill Destination:=Range("AC2:AC" & Cells(Rows.Count, "A").End(xlUp).Row)
   
On Error Resume Next
   
    Set ws = Worksheets("Menu")
   
On Error GoTo 0
   
    If Not ws Is Nothing Then
   
    Application.DisplayAlerts = False
       
        Worksheets("UsedVSB_MA5 (VS)").Delete
      
    Application.DisplayAlerts = True
       
    End If
 
    Sheets("Stock").Columns.AutoFit
    Sheets("Menu").Range("SIDATE") = Date
    Sheets("Menu").Activate
   
    ans2 = MsgBox("The Stock file has been updated succesfully!", vbOKOnly + vbInformation, "Success!")
   
    ActiveWorkbook.Saved = True
    Application.ScreenUpdating = True
 
    End If
 
    Exit Sub
 
ErrorHandler_ImportData:
   
    ans3 = MsgBox("The Stock File has not been imported!", vbCritical + vbInformaiton, "WARNING: No Import Found!")
   
    Application.ScreenUpdating = True
   
    Exit Sub
 
End Sub

 

[JLGWhiz]

Then Maybe this is what you are looking for. It combines the cell differences check with the dropped item check.

    For Each c In sh2.Range("G2", sh2.Cells(Rows.Count, 7).End(xlUp))
        Set fn = sh1.Range("G:G").Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                With sh2
                    For i = 1 To 22
                        If i < 5 Or i > 9 Then
                            If .Cells(c.Row, i).Value <> sh1.Cells(fn.Row, i).Value Then
                              sh1.Cells(fn.Row, i).Value = .Cells(c.Row, i)
                            End If
                        End If
                    Next
                End With
            Else
                c.EntireRow.Copy sh1.Cells(Rows.Count, 1).End(xlUp)(2)
            End If
    Next