Vlookup in multiple sheets

[Nando1988]

I want to make a vba function that looks for a value from a range in sheet "Data", and looks for all the values in column A of that sheet in all of the following sheets. if a match is found, a summary sheet will be created, and it will contain all the values found and the accumulated addition of the numeric values in column B of the matches in all of the sheets.
This is what I have so far, but its giving me an error, and I don't know how to fix it.
Any help will be greatly appreciated.
Thanks.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dim ws As Worksheet
Dim strWhat As String
Dim rngSearch, rng As Range
Dim rngFound As String
Dim i As Integer
Set rng = Range("A2:A1048576")[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]For Each cell In rng
value = 0
cantidad = 0
precio = 0
unidad = cell
    For Each ws In ActiveWorkbook.Worksheets
    Set rngSearch = ws.Cells.Find(What:=cell.value)
    cantidad1 = Application.VLookup(cell, ws.Range("A2:A1048576"), 2, False)
    cantidad = cantidad
        If Not rngSearch Is Nothing Then
        i = i + 1
        If i = 1 Then
            rngFound = rngSearch.Worksheet.Name
        Else
            rngFound = rngFound & ", " & rngSearch.Worksheet.Name
        End If
            precio = precio + Application.WorksheetFunction.VLookup(cell, ws.Range("A2:A1048576"), 3, False)
            valor = valor + Application.WorksheetFunction.VLookup(cell, ws.Range("A2:A1048576"), 4, False)
            ' Insert your code here.
            ' The following line shows how to reference a sheet within
            ' the loop by displaying the worksheet name in a dialog box.
    Next ws
Next cell[/FONT]

 

[DanteAmor]

I guess on the Data sheet in column A, starting at A2, you'll have something like this:


Sheet Data
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >VALUE</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >a6</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >a8</td></tr></table>


You must have 2 sheets called: Data and Summary.


The macro will search for each item on all sheets and will put the item and 3 columns on the summary sheet.


Try and tell me:

Sub Vlookup_In_Multiple_Sheets()
  Dim sh1 As Worksheet, sh2 As Worksheet, sh As Worksheet, c As Range, f As Range, j As Long
  
  Set sh1 = Sheets("Data")
  Set sh2 = Sheets("Summary")
  sh2.Rows("2:" & Rows.Count).ClearContents
  
  j = 2
  For Each c In sh1.Range("A2", sh1.Range("A" & Rows.Count).End(xlUp))
    For Each sh In Sheets
      Select Case sh.Name
        Case sh1.Name, sh2.Name
        Case Else
          Set f = sh.Range("A:A").Find(c, , xlValues, xlWhole)
          If Not f Is Nothing Then
            sh2.Cells(j, "A").Value = c
            sh2.Cells(j, "B").Value = f.Offset(, 1)
            sh2.Cells(j, "C").Value = f.Offset(, 2)
            sh2.Cells(j, "D").Value = f.Offset(, 3)
            j = j + 1
          End If
      End Select
    Next
  Next
  MsgBox "End"
End Sub