# Cell Formulas not working, why?

• ### Question

• I have a formula I'm trying to put into certain cells automatically but I keep getting an error "1004 Error: Application-defined or Object-defined error" Can anyone look at what I have and see why it's not working properly? Thanks for any and all help!

```Pfld = """P"""
Ffld = """F"""
NAfld = """NA"""
rw = ActiveSheet.UsedRange.Rows.Count - 1

For k = 2 To rw + 1
keyloc = k + 3

fnc1 = "=IF(COUNTIF(RC[-51]:RC-" & icritcols & "]," & Ffld & ")>=1," & Ffld
fnc1 = fnc1 & ",IF(COUNTIF(RC[-51]:RC[-" & icritcols & "]," & Pfld & ")=R3C" & icritcols + 2
fnc1 = fnc1 & "," & Pfld & "," & NAfld & "))"
Debug.Print fnc1
Worksheets("Data Entry").Cells(keyloc, 54) = fnc1```

﻿

Wednesday, March 27, 2013 1:38 PM

• You left out a bracket:

fnc1 = "=IF(COUNTIF(RC[-51]:RC-" & icritcols &

fnc1 = "=IF(COUNTIF(RC[-51]:RC[-" & icritcols &

When you want to create a formula using VBA, get the formula working in the cell and record a macro by selecting the cell, pressing F2, and <Enter>    Then modify that code - making sure you keep brackets correct.

• Marked as answer by Wednesday, March 27, 2013 3:32 PM
Wednesday, March 27, 2013 3:10 PM

### All replies

• You left out a bracket:

fnc1 = "=IF(COUNTIF(RC[-51]:RC-" & icritcols &

fnc1 = "=IF(COUNTIF(RC[-51]:RC[-" & icritcols &

When you want to create a formula using VBA, get the formula working in the cell and record a macro by selecting the cell, pressing F2, and <Enter>    Then modify that code - making sure you keep brackets correct.

• Marked as answer by Wednesday, March 27, 2013 3:32 PM
Wednesday, March 27, 2013 3:10 PM
• I should have added that there is no need to loop:

Worksheets("Data Entry").Cells(5, 54).Resize(rw).FormulaR1C1 = fnc1

as in

```Pfld = """P"""
Ffld = """F"""
NAfld = """NA"""
rw = ActiveSheet.UsedRange.Rows.Count - 1

fnc1 = "=IF(COUNTIF(RC[-51]:RC[-" & icritcols & "]," & Ffld & ")>=1," & Ffld
fnc1 = fnc1 & ",IF(COUNTIF(RC[-51]:RC[-" & icritcols & "]," & Pfld & ")=R3C" & icritcols + 2
fnc1 = fnc1 & "," & Pfld & "," & NAfld & "))" Worksheets("Data Entry").Cells(5, 54).Resize(rw).FormulaR1C1 = fnc1```
Wednesday, March 27, 2013 4:02 PM