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
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
Code:
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 |