VBA to Populate Table with Dynamic Formulas

[GMLee]

Excel 2007
Windows XP

Hello

I need to populate a table with two formulas. The numbers of rows and number of column will vary.
I've got my code up to the point where the table is outlined:

[TABLE="class: grid, width: 610"]
<tbody>[TR]
[TD]Employee Name[/TD]
[TD]Job Title[/TD]
[TD="align: right"]7/22/12[/TD]
[TD="align: right"]7/23/12[/TD]
[TD="align: right"]7/24/12[/TD]
[/TR]
[TR]
[TD]Employee1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The formulas I need to input are: (my apologies for the clunky formulas)
[TABLE="class: grid, width: 1706"]
<tbody>[TR]
[TD]Employee Name[/TD]
[TD]Job Title[/TD]
[TD="align: right"]7/22/12[/TD]
[TD="align: right"]7/23/12[/TD]
[TD="align: right"]7/24/12[/TD]
[/TR]
[TR]
[TD]Employee1[/TD]
[TD]=VLOOKUP(D2,Job_Title!A:B,2,FALSE)[/TD]
[TD]=IFERROR(VLOOKUP(CONCATENATE($D2,F$1),$A:$B,2,FALSE),"OFF")[/TD]
[TD]=IFERROR(VLOOKUP(CONCATENATE($D2,G$1),$A:$B,2,FALSE),"OFF")[/TD]
[TD]=IFERROR(VLOOKUP(CONCATENATE($D2,H$1),$A:$B,2,FALSE),"OFF")[/TD]
[/TR]
[TR]
[TD]Employee2[/TD]
[TD]=VLOOKUP(D3,Job_Title!A:B,2,FALSE)[/TD]
[TD]=IFERROR(VLOOKUP(CONCATENATE($D3,F$1),$A:$B,2,FALSE),"OFF")[/TD]
[TD]=IFERROR(VLOOKUP(CONCATENATE($D3,G$1),$A:$B,2,FALSE),"OFF")[/TD]
[TD]=IFERROR(VLOOKUP(CONCATENATE($D3,H$1),$A:$B,2,FALSE),"OFF")[/TD]
[/TR]
[TR]
[TD]Employee3[/TD]
[TD]=VLOOKUP(D4,Job_Title!A:B,2,FALSE)[/TD]
[TD]=IFERROR(VLOOKUP(CONCATENATE($D4,F$1),$A:$B,2,FALSE),"OFF")[/TD]
[TD]=IFERROR(VLOOKUP(CONCATENATE($D4,G$1),$A:$B,2,FALSE),"OFF")[/TD]
[TD]=IFERROR(VLOOKUP(CONCATENATE($D4,H$1),$A:$B,2,FALSE),"OFF")[/TD]
[/TR]
[TR]
[TD]Employee4[/TD]
[TD]=VLOOKUP(D5,Job_Title!A:B,2,FALSE)[/TD]
[TD]=IFERROR(VLOOKUP(CONCATENATE($D5,F$1),$A:$B,2,FALSE),"OFF")[/TD]
[TD]=IFERROR(VLOOKUP(CONCATENATE($D5,G$1),$A:$B,2,FALSE),"OFF")[/TD]
[TD]=IFERROR(VLOOKUP(CONCATENATE($D5,H$1),$A:$B,2,FALSE),"OFF")[/TD]
[/TR]
[TR]
[TD]Employee5[/TD]
[TD]=VLOOKUP(D6,Job_Title!A:B,2,FALSE)[/TD]
[TD]=IFERROR(VLOOKUP(CONCATENATE($D6,F$1),$A:$B,2,FALSE),"OFF")[/TD]
[TD]=IFERROR(VLOOKUP(CONCATENATE($D6,G$1),$A:$B,2,FALSE),"OFF")[/TD]
[TD]=IFERROR(VLOOKUP(CONCATENATE($D6,H$1),$A:$B,2,FALSE),"OFF")[/TD]
[/TR]
</tbody>[/TABLE]

With a copy paste values at the end the result should look like this:
[TABLE="class: grid, width: 610"]
<tbody>[TR]
[TD]Employee Name[/TD]
[TD]Job Title[/TD]
[TD="align: right"]7/22/12[/TD]
[TD="align: right"]7/23/12[/TD]
[TD="align: right"]7/24/12[/TD]
[/TR]
[TR]
[TD]Employee1[/TD]
[TD]Admin[/TD]
[TD]09:30-20:00[/TD]
[TD]09:30-20:00[/TD]
[TD]09:30-20:00[/TD]
[/TR]
[TR]
[TD]Employee2[/TD]
[TD]Admin[/TD]
[TD]12:30-23:00[/TD]
[TD]12:30-23:00[/TD]
[TD]12:30-23:00[/TD]
[/TR]
[TR]
[TD]Employee3[/TD]
[TD]Admin[/TD]
[TD]08:30-17:45[/TD]
[TD]09:15-17:45[/TD]
[TD]08:30-17:45[/TD]
[/TR]
[TR]
[TD]Employee4[/TD]
[TD]Admin[/TD]
[TD]11:30-20:00[/TD]
[TD]14:30-23:00[/TD]
[TD]14:30-23:00[/TD]
[/TR]
[TR]
[TD]Employee5[/TD]
[TD]Admin[/TD]
[TD]14:30-23:00[/TD]
[TD]14:30-23:00[/TD]
[TD]14:00-22:30[/TD]
[/TR]
</tbody>[/TABLE]

JoeMo answered a question for me yesterday to copy a formula down one column for a varying number of rows. I've been trying to make modifications to that code to complete this new task. What I have so far is

Dim lastRow As Long, rd As Long, lastcol As Long, ws As Worksheet, cd As Long

Set ws = ActiveSheet


    lastRow = ws.Range("D" & Rows.Count).End(xlUp).Row
    lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
   
    


    With ws
    
        For rd = 2 To lastRow
            If Len(Trim(.Range("d" & rd).Value)) <> 0 Then _
            .Range("e" & rd).Formula = "=VLOOKUP(RC[-1],Sheet3!C[-4]:C[-3],2,FALSE)"
            .Range("f" & rd).Formula = "=VLOOKUP(CONCATENATE(RC[-2],2C),C[-5]:C[-4],2,FALSE)"
        Next rd
        'do until
    End With

Which really I've only been able to figure out how to count the number of column I have. The column in the table start at D.

Thanks for reading!

 

[bibleguy125]

This is what I've used in the past with much success

Dim source As Range
Dim fill As Range
Dim ws1 As Worksheet

Set ws1 = Worksheets("Sheet1")

Set source = ws1.Range("A1")
            Set fill = ws1.Range("A1:A5")
            ws1.Range("A1").Formula = "=your formula here"
            source.AutoFill Destination:=fill

 

[GMLee]

Thank you bibleguy125

The code you posted is almost what I am looking for except I need the macro to be able to fins and define the range as it will differ each time. I have managed to get the second formula working in column f now I just need to fill to the last column.

Dim lastRow As Long, rd As Long, lastcol As Long, ws As Worksheet, cd As Long


Set ws = ActiveSheet


    lastRow = ws.Range("D" & Rows.Count).End(xlUp).Row
    lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
   
    


    With ws
    
        For rd = 2 To lastRow
            If Len(Trim(.Range("d" & rd).Value)) <> 0 Then _
            .Range("e" & rd).Formula = "=VLOOKUP(RC[-1],Job_Title!C[-4]:C[-3],2,FALSE)"
            .Range("f" & rd).Formula = "=iferror(VLOOKUP(CONCATENATE(RC4,R1C),C1:C2,2,FALSE),""OFF"")"
        Next rd
        
    End With

 

[Norie]

Try this.

Set ws = ActiveSheet

LastRow = ws.Range("D" & Rows.Count).End(xlUp).Row

LastCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column

Set rng = ws.Range("E2", ws.Cells(LastRow, LastCol))

rng.Columns(1).Formula = "=VLOOKUP(D2, Job_Title!A:B, 2, 0)"

rng.Offset(, 1).Resize(, rng.Columns.Count - 1).Formula = "=IFERROR(VLOOKUP(CONCATENATE($D2,F$1),$A:$B,2,FALSE),""OFF"")"

 

[GMLee]

Works perfectly. Thank you Norie!