VBA to vlookup in a workbook

[tazz]

Hello all,
I need help with a VBA that will bring data into a table as shown in the attached excel file.
I hope there are enough explanations inside the excel file.
Thank you

 

[tazz]

Perhaps I need to bring more explanations:
1. Data in "REPORT" represents an initial estimation and it will be changed with data from sheets(pieces) only for the pieces that I have a spreadsheet;
2. All other data will stay unchanged until a new sheet(piece) will be inserted.
In thread's name I said that I need a VBA for a Vlookup but I am open to any kind of VBA or solution that will solve this problem.
Thank you

 

[SirJB7]

Hi, tazz!

Give a look at the uploaded file. Columns X:Z contain the formulas used to retrieve the value found in worksheets piece1 to piece7 in range X:AA, and columns AA:AC have the required values.

X:Z array formula: =BUSCARV($T2;INDIRECTO("'"&INDICE($T$2:$T$8;COINCIDIR(VERDADERO;CONTAR.SI(INDIRECTO("'"&$T$2:$T$8&"'!$x:$x");$T2)>0;0))&"'!$x:$aa");COLUMNA()-22;0) -----> in english: =VLOOKUP($T2,INDIRECT("'"&INDEX($T$2:$T$8,MATCH(TRUE,COUNTIF(INDIRECT("'"&$T$2:$T$8&"'!$x:$x"),$T2)>0,0))&"'!$x:$aa"),COLUMN()-22,0)

Remember that array formulas should be entered with Ctrl-Shift-Enter instead of just Enter.

Just advise if any issue.

Regards!

PS: Source: http://xl-central.com/lookup-single-criteria-multiple-sheets.html

EDITED

PS2: Ooops... it was a macro requirement, didn't read it. Well, if nobody appears I'll try to come back later. In the meanwhile play a bit with the formula solution

 

[SirJB7]

Hi, tazz!

Give a look at the uploaded file. In the class module of worksheet REPORT there is the following macro:

Option Explicit
 
Sub WorkTwiceBecauseOfNotReadingCarefully()
    ' constants
    Const ksWSFixed = "REPORT"
    Const ksFixedRange = "T:W"
    Const ksOtherRange = "X:AA"
    ' declarations
    Dim rngFixed As Range, rngOther As Range
    Dim I As Long, J As Integer, K As Long, L As Integer, bOk As Boolean, A As String
    ' start
    Set rngFixed = Worksheets(ksWSFixed).Range(ksFixedRange)
    ' process
    With rngFixed
        For I = 2 To .Rows.Count
            A = .Cells(I, 1).Value
            If A = "" Then Exit For
            bOk = False
            For J = 1 To Worksheets.Count
                If Worksheets(J).Name <> ksWSFixed Then
                    Set rngOther = Worksheets(J).Range(ksOtherRange)
                    For K = 2 To Worksheets(J).Rows.Count
                        If rngOther.Cells(K, 1).Value = "" Then Exit For
                        If rngOther.Cells(K, 1).Value = A Then
                            bOk = True
                            Exit For
                        End If
                    Next K
                    If bOk Then Exit For
                    Set rngOther = Nothing
                End If
            Next J
            If bOk Then
                For L = 2 To 4
                    .Cells(I, L).Value = rngOther.Cells(K, L).Value
                Next L
            End If
        Next I
    End With
    ' end
    Set rngOther = Nothing
    Set rngFixed = Nothing
    Beep
End Sub

I deleted the 1st 3 empty rows in worksheet piece2 since it's required that the search range starts at row 1.

Regards!

 

[tazz]

Hello Sirjb
Thank you for helping with this problem.
I will test it Monday morning and I will let you know how it is working(I have excel 2010 only at work).
Once again, thank you so much.

 

[tazz]

Hi SirJB,
I tested the macro and is running nice and smooth.
Thank you so much

 

[SirJB7]

Hi, tazz!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!