asked on

Update Modification

I had this question after viewing Update Modification.

Hi experts,
need a help with VBA code .please help me out ...
i have 3 sheets in attached WB. Import - Master - ReworkMaster.
in attached there VBA code which post row entries from sheet import to sheet master, in sheet import - column A if values Add is there then it post new row in sheet master ....if there is update then it update data in sheet master from import sheet & if there is Update/Add then code update data and post new row data in sheet master ....

i need change with last condition i.e. Update/Add...i need code
1) Update data
2) Add data
3) remove updated row from sheet master and post whole row in sheet ReworkMaster.

remove blank row from master sheet which is being transfer to sheet ReworkMaster.

Sub UpdateAdd()
    Dim Add, l, Update As Range, rngMaster As Range, rngCell As Range
    Dim BatchNumber, Found
    Dim wsImport As Worksheet, wsMaster As Worksheet
    Set wsImport = ThisWorkbook.Worksheets("Import")
    Set wsMaster = ThisWorkbook.Worksheets("Master")
    wsImport.Activate
    Found = 0
    
    For Each c In Range(Range("A2"), Range("A" & Rows.count).End(xlUp))
        Add = wsImport.Range("B" & c.Row & ":BU" & c.Row).Value
        'new
        'Update = wsImport.Range("K" & c.Row & ":AJ" & c.Row).Value
        With wsImport
            Set Update = Union(.Range("K" & c.Row), _
                               .Range("N" & c.Row), _
                               .Range("V" & c.Row), _
                               .Range("W" & c.Row), _
                               .Range("AG" & c.Row & ":AJ" & c.Row))
        End With
        With wsMaster
             Set rngMaster = Union(.Range("J" & c.Row), _
                                   .Range("M" & c.Row), _
                                   .Range("U" & c.Row), _
                                   .Range("V" & c.Row), _
                                   .Range("AF" & c.Row & ":AI" & c.Row))
       End With

       BatchNumber = Range("E" & c.Row).Value
        If c.Value = "Update" Then
            wsMaster.Activate
            For Each f In Range(Range("D2"), Range("D" & Rows.count).End(xlUp))
                If f.Value = BatchNumber Then
                    'new
'                    Range("J" & f.Row & ":AI" & f.Row).Value = Update
                    For Each rngCell In Update
                        rngMaster.Range(rngCell.Address) = Update.Range(rngCell.Address)
                    Next
                    
                End If
            Next f
            wsImport.Activate
        ElseIf c.Value = "Add" Then
            wsMaster.Activate
            l = Range("A" & Rows.count).End(xlUp).Row + 1
            Range("A" & l & ":BT" & l).Value = Add
            
            Range("AF" & l & ":AI" & l).Value = ""
            Range("V" & l).Value = "Open"
            
            wsImport.Activate
        ElseIf c.Value = "Update/Add" Then
            wsMaster.Activate
            l = Range("A" & Rows.count).End(xlUp).Row + 1
            For Each f In Range(Range("D2"), Range("D" & Rows.count).End(xlUp))
                If f.Value = BatchNumber Then
                    'new
'                    Range("J" & f.Row & ":AI" & f.Row).Value = Update
                    For Each rngCell In Update
                        rngMaster.Range(rngCell.Address) = Update.Range(rngCell.Address)
                    Next
                    
                    Exit For
                End If
            Next f
            Range("A" & l & ":BT" & l).Value = Add
            Range("AF" & l & ":AI" & l).Value = ""
            Range("V" & l).Value = "Open"
            wsImport.Activate
        End If
    Next c
End Sub

Open in new window

See attached.

Thanks
EE--1-.xlsm

Mike in IT

I believe this will do it:

    ElseIf c.Value = "Update/Add" Then
            wsMaster.Activate
            l = Range("A" & Rows.count).End(xlUp).Row + 1
            For Each f In Range(Range("D2"), Range("D" & Rows.count).End(xlUp))
                If f.Value = BatchNumber Then
                    Range("A" & f.Row).Select
                    Update = Range("A" & f.Row & "BT" & f.Row).Value
                    ActiveCell.Rows("1:1").EntireRow.Select
                    Selection.Delete Shift:=xlUp
                    wsRework.Activate
                    k = Range("A" & Rows.count).End(xlUp).Row + 1
                    Range("A" & k & "BT" & k).Value = Update
                    
                    Exit For
                End If
            Next f
            Range("A" & l & ":BT" & l).Value = Add
            Range("AF" & l & ":AI" & l).Value = ""
            Range("V" & l).Value = "Open"
            wsImport.Activate
        End If

Open in new window

Just update the ElseIf for the "Update/Add"

Naresh Patel

ASKER

there is error

Sub UpdateAdd()
    Dim Add, l, Update As Range, rngMaster As Range, rngCell As Range
    Dim BatchNumber, Found
    Dim wsImport As Worksheet, wsMaster As Worksheet
    Set wsImport = ThisWorkbook.Worksheets("Import")
    Set wsMaster = ThisWorkbook.Worksheets("Master")
    Set wsReworkMaster = ThisWorkbook.Worksheets("ReworkMaster")
    
    wsImport.Activate
    Found = 0
    
    For Each c In Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
        Add = wsImport.Range("B" & c.Row & ":BU" & c.Row).Value
        'new
        'Update = wsImport.Range("K" & c.Row & ":AJ" & c.Row).Value
        With wsImport
            Set Update = Union(.Range("K" & c.Row), _
                               .Range("N" & c.Row), _
                               .Range("V" & c.Row), _
                               .Range("W" & c.Row), _
                               .Range("AG" & c.Row & ":AJ" & c.Row))
        End With
        With wsMaster
             Set rngMaster = Union(.Range("J" & c.Row), _
                                   .Range("M" & c.Row), _
                                   .Range("U" & c.Row), _
                                   .Range("V" & c.Row), _
                                   .Range("AF" & c.Row & ":AI" & c.Row))
       End With

       BatchNumber = Range("E" & c.Row).Value
        If c.Value = "Update" Then
            wsMaster.Activate
            For Each f In Range(Range("D2"), Range("D" & Rows.Count).End(xlUp))
                If f.Value = BatchNumber Then
                    'new
'                    Range("J" & f.Row & ":AI" & f.Row).Value = Update
                    For Each rngCell In Update
                        rngMaster.Range(rngCell.Address) = Update.Range(rngCell.Address)
                    Next
                    
                End If
            Next f
            wsImport.Activate
        ElseIf c.Value = "Add" Then
            wsMaster.Activate
            l = Range("A" & Rows.Count).End(xlUp).Row + 1
            Range("A" & l & ":BT" & l).Value = Add
            
            Range("AF" & l & ":AI" & l).Value = ""
            Range("V" & l).Value = "Open"
            
            wsImport.Activate
        ElseIf c.Value = "Update/Add" Then
            wsMaster.Activate
            l = Range("A" & Rows.Count).End(xlUp).Row + 1
            For Each f In Range(Range("D2"), Range("D" & Rows.Count).End(xlUp))
                If f.Value = BatchNumber Then
                    Range("A" & f.Row).Select
                    Update = Range("A" & f.Row & "BT" & f.Row).Value
                    ActiveCell.Rows("1:1").EntireRow.Select
                    Selection.Delete Shift:=xlUp
                    wsReworkMaster.Activate
                    k = Range("A" & Rows.Count).End(xlUp).Row + 1
                    Range("A" & k & "BT" & k).Value = Update
                    
                    Exit For
                End If
            Next f
            Range("A" & l & ":BT" & l).Value = Add
            Range("AF" & l & ":AI" & l).Value = ""
            Range("V" & l).Value = "Open"
            wsImport.Activate
        End If
    Next c
End Sub

Open in new window

EE--1---1-.xlsm

Mike in IT

Sorry, missed the ":" in that line

Update = Range("A" & f.Row & ":BT" & f.Row).Value

Open in new window

Naresh Patel

ASKER

one more error after updating with above line

just for information Update ranges or values being change then what you have provided in code. See This.

Thanks

Mike in IT

Same problem, I forgot the ":"

Range("A" & k & ":BT" & k).Value = Update

Open in new window

Naresh Patel

ASKER

Seems too much errors

i need change with last condition i.e. Update/Add.

Condition Update/Add Process Flow.
1) Update data
2) Add data
3) remove updated row from sheet master and post whole row in sheet ReworkMaster.

Old code perform 1 & 2 for type Update/Add....need amendment regarding 3rd point.
transfer updated data which match condition Update/Add (point 1) from sheet to master to sheet MasterRework (entries with being updated i.e. import sheet column A =Update/Add).

Thanks

Mike in IT

I'm not understanding what you are asking for. The updated code I sent did this:

ElseIf c.Value = "Update/Add" Then
	wsMaster.Activate
	l = Range("A" & Rows.count).End(xlUp).Row + 1
'Check each value in Column D
	For Each f In Range(Range("D2"), Range("D" & Rows.count).End(xlUp))
'Compare to the found Batch Number from the Import sheet
		If f.Value = BatchNumber Then
'Select the first cell of the row that has a match
			Range("A" & f.Row).Select
'take the values from the entire row into a variable to be used later
			Update = Range("A" & f.Row & ":BT" & f.Row).Value
'Select the entire row
			ActiveCell.Rows("1:1").EntireRow.Select
'Delete the selected row and shift everything up
			Selection.Delete Shift:=xlUp
'Switch to the ReworkMaster sheet
			wsRework.Activate
'Find the first unused row
			k = Range("A" & Rows.count).End(xlUp).Row + 1
'add the line from the previously matched Master sheet that had the Batch Number
			Range("A" & k & ":BT" & k).Value = Update
			Exit For
		End If
	Next f
'return to the master sheet
	wsMaster.Activate
'add the values that were found on the Import sheet
	Range("A" & l & ":BT" & l).Value = Add
'the next two row update the data that was just added to the Master sheet
	Range("AF" & l & ":AI" & l).Value = ""
	Range("V" & l).Value = "Open"
'Return to the Import sheet to find the next Batch Number
	wsImport.Activate
End If

Open in new window

From what I understand of what you want this will work. It does the following:

Checks each value in the D column on the Master sheet
Compares it to the Found Batch Number from the Import sheet
When they match it will select the first column of the row that the match was found on
copies the data from the row with the match
selects the entire row and deletes it moving everything up
switches to the ReworkMaster sheet
finds the first unused row
adds the row that was taken from the Master sheet
returns to the master sheet
adds the data from the Import sheet
Updates the data
returns to the Import sheet to find the next Batch Number

Naresh Patel

ASKER

Original code which i posted does ....for cases A) Update
B) Add C) Update/Add...all done well ...need modification in case C)
case C) dose i.e. Update/Add
1) Update data for every match batch number
2) Add same row

modification
transfer entries which being updated (1) in master sheet to sheet MasterRework.

ideais that i dont need same batch number in same sheet ...

Your last commnt will investigate and revert you ...as right now i cant able to excess my machine ..

idea is

Naresh Patel

ASKER

and after transfering uodated data to sheet masterrework ...Add row from sheet import to sheet master .

replying via Mobile so difficult to analayze

Thanks

Mike in IT

I am not understanding what you are asking for. What the sub that I provided does is for the "Update/Add" rows it checks if there is a matching row in the Master sheet and moves it to the ReworkMaster sheet, then it adds the row from the Import sheet to the Master sheet. If that is not correct then please explain better what you are wanting it to do.

Naresh Patel

ASKER

See attached Pictures