Help with concatenation of a formula.

[Belair58]

Hello,

I'm finding the last row with data in my range and then I'm attempting to use the cell number in a formula.

'''

firstRow = sht.Cells(sht.Rows.Count, "D").End(xlDown).Row
lastRow = sht.Cells(sht.Rows.Count, "D").End(xlUp).Row


Range("E1").Select
ActiveCell.FormulaR1C1 = "Late / OnTime"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=RC[-4]="""""
Range("E2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-4]="""","""",IFERROR(VLOOKUP(RC[-4], Cycle_" & Cycle & "_Output.txt!C1,1,FALSE),""LATE""))"
Range("E2").Select
Selection.Copy
Range("E3").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Columns("E:E").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False


Range("E" & lastRow + 1).Select
ActiveCell.FormulaR1C1 = "=ROUND((COUNTIF(E2:" & lastRow",""LATE"")/COUNTA(E2:" & lastRow & ")*100),2)"

'''

I'm receiving a Run-time error 1004
Application-defined or object-defined error

I'm assuming it's because I have the format of the formula incorrect.

Any help is always appreciated.

I've tried wrapping

 

[mole999]

untested

ActiveCell.FormulaR1C1 = "=ROUND((COUNTIF(E2:E" & lastRow",""LATE"")/COUNTA(E2:E" & lastRow & ")*100),2)"

 

[Belair58]

And kids, that's why you don't drink before you go to work.....

Thanks for finding my stupid mistake. However, when I do add in the E, I get this in my formula.

'''
=ROUND((COUNTIF('E2':'E69',"LATE")/COUNTA('E2':'E69')*100),2)
'''

I'm not certain how to eliminate the single quotes.

Thanks again.

 

[Belair58]

I found that it was because of the combination of ActiveCell.FormulaR1C1 and calling the cell directly.

It's not pretty but this fixed it.


'''
Range("E" & lastRow + 1).Select
ActiveCell.FormulaR1C1 = "=ROUND((COUNTIF(E2:E" & lastRow & ",""LATE"")/COUNTA(E2:E" & lastRow & ")*100),2)"
Range("E" & lastRow + 1).Formula = Replace(Range("E" & lastRow + 1).Formula, "'", "")
'''

 

[Fluff]

You're trying to use an R1C1 formula, but inputing a formula with A1 notation instead. Try

Range("E" & Lastrow + 1).Formula = "=ROUND((COUNTIF(E2:E" & Lastrow & ",""LATE"")/COUNTA(E2:E" & Lastrow & ")*100),2)"