Vlookup & Cut Row

uncxx

New Member
Joined
Jun 4, 2018
Messages
2
Hello, bit of a VBA newbie here coming for some (hopeful!) guidance. Below you will find my code for an ongoing project I am working through. My goal with this macro is to update the master sheet off a generated report that is sent to me using Vlookup. If the lookup value is found on the generated report but not the master, the entire row would be cut and added to the master. Any suggestions to help getting to a functional place would be greatly appreciated!

Code:
[/FONT][/SIZE]Option ExplicitSub SQUpdater()
    
'Define lookup value
Dim EmpEmail As String
'Define lookup array
Dim UltiProR As Range
'Define EmpInfo
Dim EmpInfo As String
'Define results array
Dim SQ As Range
'Define result cell
Dim resultRow As Long
'Define count to get to last item in the lookup column
Dim finalRow As Long
'Define counter
Dim i As Long
'Set lookup array
Set UltiProR = Sheets("UPWB").Range("A2:H351")
'Set result array
Set SQ = Sheets("Master").Range("A2:H800")
'establish final row
finalRow = Cells(Rows.Count, 1).End(xlUp).Row
            
    'Loops
    For i = 2 To finalRow
    'Set employee email/lookup value
    EmpEmail = Sheets("UPWB").Cells(i, 4).Value
    'if an error occurs, go to next line and clear the error
    On Error Resume Next
    Err.Clear
    'Perform Vlookup
    EmpInfo = Application.WorksheetFunction.VLookup(EmpEmail, SQ, 4, False)
        If EmpEmail <> 0 Then
            Sub CutRows()
            End If
        Next i
            
End Sub
    
    
Sub CutRows()


For Each ws In Sheets
'moves through every worksheet
    With ws
        If .Name = "UPWB" Then
        lastrow = .Range("A" & Rows.Count).End(xlUp).Row
        'determines the lastrow
            For i = lastrow To 1 Step -1
            'moves from bottom to top
                If (.Range("D" & i).Value) <> EmpEmail
                Then (.Range("D" & i).EntireRow.Cut,
                Destination:=Master.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
            Next i
        End If
    End With
End Sub

Thanks,
uncxx
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Forum statistics

Threads
1,215,785
Messages
6,126,887
Members
449,347
Latest member
Macro_learner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top