none
vLOOKUP fUNCTION UNABLE TO UESE RRS feed

  • Question

  • Hi,

    I'm trying t use VLookup using vba script but I'm not able to use even what I did that I recorded the marco for the same and then it was working properly but if the range is increasing or decreasing then that function is not working in my recorded marco.

    Please suggest that what should I do now. as this project is need to deliver urgently. the code I given blow what I,m using

    Set ws = ThisWorkbook.Sheets("Payroll")

    Range("B2:L2").Select
        Selection.FormulaArray = _
            "=VLOOKUP(RC1,Summery!R[-1]C4:R2127C50,{3,5,6,7,8,9,15,16,47,17,21},0)"
        ActiveWindow.SmallScroll ToRight:=1
       
        Selection.AutoFill Destination:=Range("B2:L2550")
        Range("B2:L2550").Select
        Cells.Select
        Range("B1").Activate
        Cells.EntireColumn.AutoFit
        Columns("D:D").Select
        Selection.NumberFormat = "m/d/yyyy"
        Columns("E:E").Select
        Selection.NumberFormat = "[$-F400]h:mm:ss AM/PM"
        Columns("F:F").Select
        Selection.NumberFormat = "m/d/yyyy"
        Columns("G:G").Select
        Selection.NumberFormat = "[$-F400]h:mm:ss AM/PM"
        ActiveWindow.Zoom = 85
        Range("C3").Select
        Selection.End(xlUp).Select
        Selection.End(xlToLeft).Select
        Range("B2").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range("B2:O2").Select
        Range(Selection, Selection.End(xlDown)).Select
        Range("B2:O2550").Select
        Selection.Copy
        Application.CutCopyMode = False
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Selection.End(xlUp).Select
        Selection.End(xlToLeft).Select
        Application.CutCopyMode = False
        ActiveWorkbook.Save

    Tuesday, February 20, 2018 1:03 PM

Answers

  • Don't use rows in your lookup range, and choose a column to base the size of the array on - this uses column A of worksheet Payroll - which is where, I assume, the formula is being placed.

    Sub Macro2()
        Dim lngR As Long
        Dim ws As Worksheet
        
        Set ws = ThisWorkbook.Sheets("Payroll")
        
        lngR = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        ws.Range("B2:L2").FormulaArray = "=VLOOKUP($A2,Summery!$D:$AX,{3,5,6,7,8,9,15,16,47,17,21},0)"
        ws.Range("B2:L2").Copy ws.Range("B3:L" & lngR)
        ws.Cells.EntireColumn.AutoFit
        ws.Columns("D:D").NumberFormat = "m/d/yyyy"
        ws.Columns("E:E").NumberFormat = "[$-F400]h:mm:ss AM/PM"
        ws.Columns("F:F").NumberFormat = "m/d/yyyy"
        ws.Columns("G:G").NumberFormat = "[$-F400]h:mm:ss AM/PM"
        With Intersect(ws.UsedRange, ws.Range("B:O"))
            .Value = .Value
        End With

    End Sub





    Tuesday, February 20, 2018 4:34 PM
  • Create an array of the values that you want to use to delete rows, as shown below.

    Sub TestMacro()
        Dim rngC As Range
        Dim rngV As Range
        Dim arrV As Variant
        Dim varR As Variant
        Dim Sh As Worksheet

        'array of value to base removal on - add all your other values in quotes and comma delimited
        arrV = Array("Ops Description is incorrect", "Airfare Query", "Travel Allowance", "CCF Query")

        Set Sh = Sheets("Leaver")

        With Sh

            'Set the range to evaluate to rngV
            Set rngV = .Range(.Range("J2"), .Cells(.Rows.Count, "J").End(xlUp))

            'Set the flags
            rngV.Offset(0, 1).EntireColumn.Insert
            rngV.Offset(0, 1).Value = "Keep"

            .Range("K1").Value = "Action"

            'Check the cells
            For Each rngC In rngV
                varR = Application.Match(rngC.Value, arrV, False)
                If Not IsError(varR) Then
                    rngC.Offset(0, 1).Value = "Remove"
                End If
            Next rngC


            With .Sort
                .SortFields.Clear
                .SortFields.Add Key:=Range("K2"), SortOn:=xlSortOnValues, Order:=xlAscending
                .SetRange Sh.UsedRange
                .Header = xlYes
                .Orientation = xlTopToBottom
                .Apply
            End With

            Set rngC = .Range("K:K").Find(What:="Remove", After:=.Range("K1"), LookIn:=xlValues, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=True, SearchFormat:=False)

            .Range(rngC, .Cells(.Rows.Count, "K").End(xlUp)).EntireRow.Delete
            .Range("K:K").EntireColumn.Delete

        End With

    End Sub

                                                                                            
    • Marked as answer by Gajendra Pandey Wednesday, February 28, 2018 12:02 PM
    Monday, February 26, 2018 6:50 PM

All replies

  • Don't use rows in your lookup range, and choose a column to base the size of the array on - this uses column A of worksheet Payroll - which is where, I assume, the formula is being placed.

    Sub Macro2()
        Dim lngR As Long
        Dim ws As Worksheet
        
        Set ws = ThisWorkbook.Sheets("Payroll")
        
        lngR = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        ws.Range("B2:L2").FormulaArray = "=VLOOKUP($A2,Summery!$D:$AX,{3,5,6,7,8,9,15,16,47,17,21},0)"
        ws.Range("B2:L2").Copy ws.Range("B3:L" & lngR)
        ws.Cells.EntireColumn.AutoFit
        ws.Columns("D:D").NumberFormat = "m/d/yyyy"
        ws.Columns("E:E").NumberFormat = "[$-F400]h:mm:ss AM/PM"
        ws.Columns("F:F").NumberFormat = "m/d/yyyy"
        ws.Columns("G:G").NumberFormat = "[$-F400]h:mm:ss AM/PM"
        With Intersect(ws.UsedRange, ws.Range("B:O"))
            .Value = .Value
        End With

    End Sub





    Tuesday, February 20, 2018 4:34 PM
  • Thank you  so much. it helps a lot for me.

    but there is another suggestion i wanted to know.. as i'm using below script for particular row deletion containing few words.

                   

     Sheets("Leaver").Activate

      'Set the range to evaluate to rng.
        Set rng = Range("J2:J3500")

        'Loop backwards through the rows
        'in the range that you want to evaluate.
        For i = rng.Rows.Count To 1 Step -1

            'If cell i in the range contains an "x", delete the entire row.
            If rng.Cells(i).Value = "Ops Description is incorrect" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "Airfare Query" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "Travel Allowance" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "CCF Query" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "Cash List - Feb'18" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "DOJ (Pension)" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "Salary Advance" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "Call Transactions" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "Payslip" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "Onboarding" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "Posting Simulation Error" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "IQ Posting Simulation Error" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "School Fee Query" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "Macro Tools" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "Statement of Earning" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "Statement of Earning" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "MCR" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "Housing Deduction" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "Overtime" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "DOJ" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "CCF" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "Salary Deduction Query" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "System Hiring" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "School Fee" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "Vip Chip Deduction" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "Housing Advance" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "Pension Query" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "Payroll Query" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "Visa Deduction Query" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "Hotel Deduction" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "Salary Deduction" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "ECA" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "Onboarding Query" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "UK Tax and NI Contribuation" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "Airfare Reimbursement" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "Salary Query" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "Deduction Query" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "Tax Letter" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "BDC" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "IT Issue" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "DOJ Pension Query" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "DOJ(Pension)" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "FS Query" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "DOJ(pension)" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "PDC" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = " Airfare Reimbursement" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "Pension query" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "Cash List" Then rng.Cells(i).EntireRow.Delete
            If rng.Cells(i).Value = "EID Deduction" Then rng.Cells(i).EntireRow.Delete


        Next

    End Sub

    plz suggest any short method for deletion.

    Thanks,

    Gajendra

    9793936262

    Monday, February 26, 2018 6:00 AM
  • Create an array of the values that you want to use to delete rows, as shown below.

    Sub TestMacro()
        Dim rngC As Range
        Dim rngV As Range
        Dim arrV As Variant
        Dim varR As Variant
        Dim Sh As Worksheet

        'array of value to base removal on - add all your other values in quotes and comma delimited
        arrV = Array("Ops Description is incorrect", "Airfare Query", "Travel Allowance", "CCF Query")

        Set Sh = Sheets("Leaver")

        With Sh

            'Set the range to evaluate to rngV
            Set rngV = .Range(.Range("J2"), .Cells(.Rows.Count, "J").End(xlUp))

            'Set the flags
            rngV.Offset(0, 1).EntireColumn.Insert
            rngV.Offset(0, 1).Value = "Keep"

            .Range("K1").Value = "Action"

            'Check the cells
            For Each rngC In rngV
                varR = Application.Match(rngC.Value, arrV, False)
                If Not IsError(varR) Then
                    rngC.Offset(0, 1).Value = "Remove"
                End If
            Next rngC


            With .Sort
                .SortFields.Clear
                .SortFields.Add Key:=Range("K2"), SortOn:=xlSortOnValues, Order:=xlAscending
                .SetRange Sh.UsedRange
                .Header = xlYes
                .Orientation = xlTopToBottom
                .Apply
            End With

            Set rngC = .Range("K:K").Find(What:="Remove", After:=.Range("K1"), LookIn:=xlValues, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=True, SearchFormat:=False)

            .Range(rngC, .Cells(.Rows.Count, "K").End(xlUp)).EntireRow.Delete
            .Range("K:K").EntireColumn.Delete

        End With

    End Sub

                                                                                            
    • Marked as answer by Gajendra Pandey Wednesday, February 28, 2018 12:02 PM
    Monday, February 26, 2018 6:50 PM
  • Thank you So Much Sir.

    you suggestions reduced my work.

    Thanks,

    Gajendra Pandey

    Tuesday, February 27, 2018 4:59 PM
  • Hi Sir,

    Thanks you so much for your help .

    now i have another project that is over time comparison. as i have two sheets one is for the month of December and another is for the month of January and need to compare salary for individual employee's salary variation.

    any suggestion even if i can do this in normal excel that will be also fine.

    Thanks,

    Gajendra Pandey 

    Wednesday, February 28, 2018 11:39 AM