VBA code to enter result of formula for all rows in 3 columns

[Hans_]

Hi all,

I have 3 columns in a sheet that contain formulas but i want to replace this with a value from a vba code as my sheet contains more than 10.000 lines. The formulas start at row 2. Row 1 is a header row.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]timePeriod (column A)[/TD]
[TD]timeTable (column B)[/TD]
[TD]timeCount (column C)[/TD]
[/TR]
[TR]
[TD]=VLOOKUP("W"&D2;tblTime!$A:$N;4;FALSE)[/TD]
[TD]=COUNTIF(tblTime!D:D;tblWeekData!A2)[/TD]
[TD]=IF(SUMPRODUCT(($G$1:$G2=G2)*($A$1:$A2=A2)*($D$1:$D2=D2))>1;0;1)[/TD]
[/TR]
</tbody>[/TABLE]

Usualy i would copy down the formula to the last line but i want the entire process automated and replaced by a hard value instead of the formula.

At the moment i have the following code that isn't working:

With ThisWorkbook.Sheets("tblWeekData")
    lastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
End With


With ThisWorkbook.Sheets("tblWeekData").Range("A2:A" & lastRow)
    .Offset(0, 0).Formula = "=VLOOKUP(""W""&D2;tblTime!$A:$N;" & i & ";FALSE)"
    .Offset(0, 0).Value = .Value
    .Offset(0, 1).Formula = "=IF(SUMPRODUCT(($G$1:$G2=G2)*($A$1:$A2=A2)*($D$1:$D2=D2))>1;0;1)"
    .Offset(0, 1).Value = .Value
    .Offset(0, 2).Formula = "=COUNTIF(OFFSET(tblTime!A:A;0;" & i - 1 & ";tblWeekData!A2)"
    .Offset(0, 2).Value = .Value
End With

Any help would be appreciated.

Kind regards,
Hans

 

[JackDanIce]

What isn't working? See if this helps (have made a few changes to your formula to make them calculate faster):

Dim x   As Long

With Application
    .ScreenUpdating = False
    .Calculation = xlManual
End With

With Sheets("tblWeekData")
    x = Sheets("tblTime").Cells(Rows.Count, 1).End(xlUp).row
    .Cells(2, 2).Formula = "=--NOT(SUMPRODUCT(($G$1:$G2=G2)*($A$1:$A2=A2)*($D$1:$D2=D2))>1)"
    .Cells(2, 3).Formula = "=COUNTIF(OFFSET(tblTime!$A$1:$A$" & x & ";0;" & i - 1 & ";tblWeekData!A2)"
    x = .Cells(.Rows.Count, 4).End(xlUp).row
    With .Cells(2, 1)
        .Formula = "=VLOOKUP(""W""&D2;tblTime!$A$1:$N$" & x & ";" & i & ";0)"
        .Resize(x - 1, 3).FillDown
        Parent.Calculate
        .Resize(x - 1, 3).Value = .Resize(x - 1, 3).Value
    End With
End With

With Application
    .ScreenUpdating = True
    .Calculation = xlAutomatic
End With