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:

Employee Name	Job Title	7/22/12	7/23/12	7/24/12
Employee1
Employee2
Employee3
Employee4
Employee5

<tbody>
</tbody>

The formulas I need to input are: (my apologies for the clunky formulas)

Employee Name	Job Title	7/22/12	7/23/12	7/24/12
Employee1	=VLOOKUP(D2,Job_Title!A:B,2,FALSE)	=IFERROR(VLOOKUP(CONCATENATE($D2,F$1),$A:$B,2,FALSE),"OFF")	=IFERROR(VLOOKUP(CONCATENATE($D2,G$1),$A:$B,2,FALSE),"OFF")	=IFERROR(VLOOKUP(CONCATENATE($D2,H$1),$A:$B,2,FALSE),"OFF")
Employee2	=VLOOKUP(D3,Job_Title!A:B,2,FALSE)	=IFERROR(VLOOKUP(CONCATENATE($D3,F$1),$A:$B,2,FALSE),"OFF")	=IFERROR(VLOOKUP(CONCATENATE($D3,G$1),$A:$B,2,FALSE),"OFF")	=IFERROR(VLOOKUP(CONCATENATE($D3,H$1),$A:$B,2,FALSE),"OFF")
Employee3	=VLOOKUP(D4,Job_Title!A:B,2,FALSE)	=IFERROR(VLOOKUP(CONCATENATE($D4,F$1),$A:$B,2,FALSE),"OFF")	=IFERROR(VLOOKUP(CONCATENATE($D4,G$1),$A:$B,2,FALSE),"OFF")	=IFERROR(VLOOKUP(CONCATENATE($D4,H$1),$A:$B,2,FALSE),"OFF")
Employee4	=VLOOKUP(D5,Job_Title!A:B,2,FALSE)	=IFERROR(VLOOKUP(CONCATENATE($D5,F$1),$A:$B,2,FALSE),"OFF")	=IFERROR(VLOOKUP(CONCATENATE($D5,G$1),$A:$B,2,FALSE),"OFF")	=IFERROR(VLOOKUP(CONCATENATE($D5,H$1),$A:$B,2,FALSE),"OFF")
Employee5	=VLOOKUP(D6,Job_Title!A:B,2,FALSE)	=IFERROR(VLOOKUP(CONCATENATE($D6,F$1),$A:$B,2,FALSE),"OFF")	=IFERROR(VLOOKUP(CONCATENATE($D6,G$1),$A:$B,2,FALSE),"OFF")	=IFERROR(VLOOKUP(CONCATENATE($D6,H$1),$A:$B,2,FALSE),"OFF")

<tbody>
</tbody>

With a copy paste values at the end the result should look like this:

Employee Name	Job Title	7/22/12	7/23/12	7/24/12
Employee1	Admin	09:30-20:00	09:30-20:00	09:30-20:00
Employee2	Admin	12:30-23:00	12:30-23:00	12:30-23:00
Employee3	Admin	08:30-17:45	09:15-17:45	08:30-17:45
Employee4	Admin	11:30-20:00	14:30-23:00	14:30-23:00
Employee5	Admin	14:30-23:00	14:30-23:00	14:00-22:30

<tbody>
</tbody>

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!