1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

looking for a vlookup VBA code for my report

Discussion in 'VBA Macros' started by kittu akula, Mar 16, 2017.

  1. kittu akula

    kittu akula New Member

    Messages:
    22
    i am looking for a vlookup VBA code for my project.

    i have Multiple sheets llike sheet1,sheet2,sheet3,....upto sheet7 in these sheets i have different columns and a usernames column (column B) as common. i have one more sheet8 in same excel work book in that i have only one column with name usernames (with usernames at column A )

    now i want to vlookup sheet1 to sheet7 with sheet8 and what ever names are matching should be there in report and which are not maching with usernames in sheet8 should be deleted in all sheets from sheet1 to sheet7( i.e NA should be deleted)

    Please let me know if its possible with VBA code
  2. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    Hi and welcome to the forum :)

    If you can, please upload a sample excel file with some dummy data so we can give you a more targeted answer.

    Thanks
  3. kittu akula

    kittu akula New Member

    Messages:
    22
    The sheets1-7 look like file1 and columns in sheet1-7 look like file2

    I need sheet1-7 vlookup with sheet8 (sheet8 look like file3)
    usernames which are matching should be saved and remaining which are not matching (i.e #N/A) in sheet1-7 should be deleted.

    sheet8 is only for vlookup purpose ...all data is available in sheet1-7

    Attached Files:

  4. karlbf

    karlbf New Member

    Messages:
    3
    Please upload a sample excel file with some dummy data and not jpg's
  5. kittu akula

    kittu akula New Member

    Messages:
    22
    pleas find attachment ... i have attached dummy data.. raw number may increase and decrease in sheet1-7 but sheet8 is fixed

    Attached Files:

  6. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    Hi,

    Here you go... (please refer to the attachment)

    Let me know if this helps.

    Attached Files:

    Thomas Kuriakose likes this.
  7. kittu akula

    kittu akula New Member

    Messages:
    22
    Hi,
    this works for me wonderfully ....want to know if lookup is done using column number or column heading ...because when i checked original data sheets in some sheets column B heading name is used differently like "Customer name" and as i mentioned in dummy sheets its "Username". i hope it will look according to the column number only.
  8. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    Hi,

    The code looks at the column 2 ("B") so there is no problem if the header is different. As long as the usernames are in "B" you should be fine.
    Also, the list in sheet8 must be in "A". If it isn't make sure to adjust the code to match.

    I'm glad I could help :)
    Thomas Kuriakose likes this.
  9. kittu akula

    kittu akula New Member

    Messages:
    22
    Hi,

    i am not getting correct data with this code

    upload_2017-3-20_17-17-17.png

    can you see above there are still some names with #N/A , after running the code for cross verification i did the vlookup manually and found some names which are not matching still present in report
  10. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    Hi,

    I think I know what the problem is... try the following:
    Code (vb):
    Sub delete()

        Dim arr(), msg As String
        Dim ws_lrow, ws8_lrow, i As Integer
        Dim ws As Worksheet
         
        ws8_lrow = Sheets("Sheet8").Cells(Rows.Count, 1).End(xlUp).Row

        ReDim arr(ws8_lrow)

        For i = 2 To ws8_lrow
            arr(i - 2) = Sheets("Sheet8").Cells(i, 1).Value
        Next i
     
        For Each ws In ActiveWorkbook.Sheets
            ws_lrow = ws.Cells(Rows.Count, 2).End(xlUp).Row
         
            For i = ws_lrow To 2 Step -1
                If IsInArray(ws.Cells(i, 2), arr()) = 0 Then
                    msg = msg & "User """ & ws.Cells(i, 2) & """ from: " & ws.Name & vbCrLf
                    ws.Cells(i, 2).EntireRow.delete xlShiftUp
                End If
            Next i
        Next ws
     
        If Len(msg) > 0 Then
            MsgBox "The following users have been deleted:" & vbCrLf & msg
        End If
     
    End Sub

    Private Function IsInArray(valToBeFound As Variant, arr As Variant) As Boolean

        Dim element As Variant
        On Error GoTo IsInArrayError: 'array is empty
         For Each element In arr
                If element = valToBeFound Then
                    IsInArray = True
                    Exit Function
                End If
            Next element
        Exit Function
     
    IsInArrayError:
        On Error GoTo 0
        IsInArray = False
     
    End Function
    Since we are deleting rows, we need to go from bottom to top an not the other way around, otherwise the code will skip rows.
    My mistake, shouldn't have missed that :(

    It should be working now.
    Let me know if it isn't.

    EDIT: I've added a condition to show the message only if something was deleted.
    kittu akula likes this.
  11. kittu akula

    kittu akula New Member

    Messages:
    22
    it worked but showing type mismatch at

    msg = msg & "User """ & ws.Cells(i, 2) & """ from: " & ws.Name & vbCrLf
  12. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    Hi,

    Can you upload the file where is throws that error?... in the sample provided it works fine.
  13. kittu akula

    kittu akula New Member

    Messages:
    22
    when i tried with original data i got that message ....i will try once again and update you..can i insert some more sheets and do vlookup as in array there is no integer is mentioned?
  14. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    I'm sorry, I'm not sure I got this last part, but you can have as many sheets as you like, as long as:
    1. You keep "sheet8" as the name of the sheet where the list is, or
    2. If you change the sheet's name, change it in the code as well.
    kittu akula likes this.
  15. Marc L

    Marc L Excel Ninja

    Messages:
    3,091
    Hi !

    Instead of using a worksheet name neither its index
    better is to use its CodeName as it does not change
    when worksheet is moved or renamed !

    [​IMG]

    Here for example for A1 cell of "abc" worksheet

    you can use Sheet1.[A1] or Sheet1.Range("A1")

    instead of Worksheets("abc").Range("A1") or Range("abc!A1")
    PCosta87 and kittu akula like this.
  16. kittu akula

    kittu akula New Member

    Messages:
    22
    Thanks all for the suggestions provided...its now working properly..

    this forum seems to be more active compare to other
    keep it up..

Share This Page