VBA if value exists, update values in relevant columns, else add value and relevant data

Mumps · Feb 14th 2020

Could you post an updated copy of your file and explain your new request in detail?

btwice · Feb 14th 2020

Sure, the main workbook has become pretty robust, so I've parsed out what I'm having trouble with to this workbook which contains the button/code and some sample data. If you notice on the tracker sheet, there are duplicate Order numbers, but the current code only updates the first match that it finds and moves to the next unique order number and updates that.

What I would like it to do, is update every instance of the order number, rather than just the first match it finds. Does this make sense?

Mumps · Feb 14th 2020

Try:

Code

Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, order As Range, srcWS As Worksheet, desWS As Worksheet, fnd As Range, sAddr As String
    Set srcWS = Sheets("Update From")
    Set desWS = Sheets("Tracker")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each order In srcWS.Range("A2:A" & LastRow)
        Set fnd = desWS.Range("A:A").Find(order, LookIn:=xlValues, lookat:=xlWhole)
        If Not fnd Is Nothing Then
            sAddr = fnd.Address
            Do
                fnd.Offset(, 1).Resize(, 2).Value = order.Offset(, 1).Resize(, 2).Value
                Set fnd = desWS.Range("A:A").FindNext(fnd)
            Loop While fnd.Address <> sAddr
            sAddr = ""
        End If
    Next order
    Application.ScreenUpdating = True
End Sub

Display More

btwice · Feb 14th 2020

Hm it seemed to work the first time I ran it, but I cleared out the data in the tracker (destination sheet) and ran it again and it only populates the first row. Odd that it populated everything the first run but can't get it to work again.

Mumps · Feb 15th 2020

Please attach a copy of the file that is not working.

btwice · Feb 17th 2020

Welp, I tried the same file/same code on a different computer and it works fine. Not sure what happened with the one that didn't work question about this code though, is there a way to specify which columns in the destination sheet that the data will be inserted to? Noticed the way it is right now I could only get it to paste into the 2 columns adjacent to the first column.

Mumps · Feb 17th 2020

Quote from btwice

is there a way to specify which columns in the destination sheet that the data will be inserted to

If the column will always be the same, then change the column reference (the number 1) in the code below to the number of columns to the right where the data should be inserted. For example, if you want to paste 5 columns to the right, change the 1 to 5. If the column will not always be the same then the code will have to be modified to prompt for the desired column. Please advise.

Code

fnd.Offset(, 1)

btwice · Feb 17th 2020

Awesome, got it working in my master workbook, thanks so much!

Mumps · Feb 18th 2020

My pleasure.

VBA if value exists, update values in relevant columns, else add value and relevant data

Participate now!

Share