Vlookup in vba

[tomas]

Hi

I Started macro and need help to finish it.

I hope workbook plus instructions should be enough explanatory


1. Vlookup formula needs to be insert into sheet "result" in range rows as far as column A contains number and columns as far as row 1 contains a string.
In this case I formatted cells but needs to be dynamic.

2. Actually maybe index formula would be better.

3. So lets say what should be a formula in cell b2
=I put vllokup there manually so it should be clear. But anyways lookup value from A column in sheet which correspond to the header B1 in range in columns N a return approximate value.

Thanks in advance.

 

[Nebu]

Hi:

Find the attached.

Sub award()
Application.ScreenUpdating = False

Dim r As Long, c As Long, lr As Long, lc As Long, rc As Long
Dim rng As Variant
Dim sh As Worksheet

r = List39.Cells(Rows.Count, "A").End(xlUp).Row
c = List39.Cells(1, Columns.Count).End(xlToLeft).Column

For lr = 2 To r
    For lc = 2 To c
        For Each sh In Worksheets
                rc = sh.Cells(Rows.Count, "P").End(xlUp).Row
                Set rng = sh.Range("N14:P" & rc)
                With List39
                    If .Cells(1, lc) = sh.Name Then
                        .Cells(lr, lc) = Application.WorksheetFunction.VLookup(.Cells(lr, 1), rng, 3, 1)
                    End If
                End With
        Next
    Next
Next

Application.ScreenUpdating = True

End Sub

Thanks

 

[tomas]

Thank you,

I appreciate that