populating table w/vlookup using VBA

[Hlatigo]

Hello all, i have a table i want to populate using to values for vlooup. problem i am having is keeping these values stationary when they need to and dynamic when they need to. this is the code i was trying to use.

as the for each loops down the header should stay stantionary but the lookup value on column C should go down in rows. once that column is done, the column lookup should stay parallal and start at top but the header lookup should have moved over with the for each. I hope this makes sense

Sub greeet()
For Each cell In Range("D10:J27")
        If IsEmpty(cell.Value) Then
            cell.Value = WorksheetFunction.VLookup(cell.Offset(, -4), _
            Sheets("sheet1").Range("L6:N29"), 3, False)
        End If

    Next cell
End Sub

Book2.xls
	C	D	E	F	G	H	I	J	K	L	M	N
6		200401	200402	200403	200404	200405	200406	200407		200401	1/1/2008	1
7	1/1/2008									200401	1/2/2008	2
8	1/2/2008									200401	1/3/2008	3
9	1/3/2008									200401	1/4/2008	4
10	1/4/2008									200401	1/5/2008	5
11	1/5/2008									200401	1/6/2008	6
12	1/6/2008									200401	1/7/2008	7
13	1/7/2008									200401	1/8/2008	8
14	1/8/2008									200401	1/9/2008	9
15	1/9/2008									200401	1/10/2008	10
16	1/10/2008									200401	1/11/2008	11
17	1/11/2008									200401	1/12/2008	12
18	1/12/2008									200401	1/13/2008	13
19	1/13/2008									200401	1/14/2008	14
20	1/14/2008									200401	1/15/2008	15
21	1/15/2008									200401	1/16/2008	16
22	1/16/2008									200401	1/17/2008	17
23	1/17/2008									200401	1/18/2008	18
24	1/18/2008									200402
25										200403
26										200404
27										200405
28										200406
29										200407
Sheet1

 

[Hlatigo]

i found this piece of code but I am having issues seeing if this will really help me and i think it can but not sure.

Set findrange = datasheet.Range("a:b") 
 
rangend = Cells(lets + 1,  nums + 1).Address 
Set plotrange =  Worksheets("scorecard").Range("b2:" & rangend) 
 
For Each mycell In plotrange 
    findstring = Cells(mycell.Row, 1) & Cells(1, mycell.Column) 
    mycell.Formula = "=vlookup(" & findstring & "," & findrange & ",2)" 
Next mycell

 

[Hlatigo]

ok, so this is the code that will work and do what i need it to do but i was really hoping to see how to use vlookup or indexmatch in VBA since I am under the impression that it would work faster and/or have less lines of code.

Dim wbModel As Workbook
Dim rngData As Range, rngTable As Range
Dim dblDataCounter As Double
Dim dblRowCounter As Double, dblColumnCounter As Double
Dim vntRowData As Variant, vntColumnData As Variant

'set the workbook and ranges
Set wbModel = Application.ThisWorkbook
Set rngData = wbModel.Sheets(1).Range("N6")
Set rngTable = wbModel.Sheets(1).Range("C9")

dblRowCounter = 1

'loop through all the rows
Do Until IsEmpty(rngTable.Offset(dblRowCounter, 0))
    dblColumnCounter = 1
    'loop through all the columns
    Do Until IsEmpty(rngTable.Offset(0, dblColumnCounter))
        'set the variable
        vntColumnData = rngTable.Offset(0, dblColumnCounter)
        vntRowData = rngTable.Offset(dblRowCounter, 0)
        dblDataCounter = 0
        'search the data for the variables above
        Do Until IsEmpty(rngData.Offset(dblDataCounter, 0)) Or _
        (rngData.Offset(dblDataCounter, 0) = vntColumnData And _
        rngData.Offset(dblDataCounter, 1) = vntRowData)
            dblDataCounter = dblDataCounter + 1
        Loop
        'check if found the data
        If IsEmpty(rngData.Offset(dblDataCounter, 0)) Then
        'do nothing
        Else
            rngTable.Offset(dblRowCounter, dblColumnCounter) = rngData.Offset(dblDataCounter, 2)
        End If
        'next column
        dblColumnCounter = dblColumnCounter + 1
    Loop
    'next row
    dblRowCounter = dblRowCounter + 1
Loop