none
Cell Formulas not working, why? RRS feed

  • 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

Answers

  • 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 AlSharp13 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 AlSharp13 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