• 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...

  • 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

kittu akula

New Member
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
 
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
 
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
 

Attachments

  • File1.JPG
    File1.JPG
    16.5 KB · Views: 8
  • file2.JPG
    file2.JPG
    20.8 KB · Views: 8
  • file3.JPG
    file3.JPG
    14.2 KB · Views: 8
pleas find attachment ... i have attached dummy data.. raw number may increase and decrease in sheet1-7 but sheet8 is fixed
 

Attachments

  • TEST1.xlsx
    20.1 KB · Views: 17
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.
 
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.
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 :)
 
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
 
Hi,

I think I know what the problem is... try the following:
Code:
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.
 
Hi,

Can you upload the file where is throws that error?... in the sample provided it works fine.
 
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?
 
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?
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.
 
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 !

upload_2015-11-5_21-26-14-png.23903

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")
 
Thanks all for the suggestions provided...its now working properly..

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