Pasting formulas with VBA not working

[excel12345u]

Hi there,

I have VBA that needs to copy formulas when a new row was added

in VBA code that pasting the formula is like this

        For i = lastRow + 1 To lastRow + count2 - count1
            criteriaSheet1.Range("C" & i).Formula = "=IFERROR(INDEX('Sheet2'!$F$3:$F$" & lastRow & ",MATCH(0,IF('Sheet 2'!$C$3:$C$" & lastRow & "<>3,1,IF('Sheet 2'!$A$3:$A$" & lastRow & "<>B" & i & ",1,COUNTIF($C$12:C" & i - 1 & ",'Sheet 2'!$F$3:$F$" & lastRow & ")))),0),"""")"
        Next i

Pasted formula is not working because in the cell when I click I can see that there are additional @ signs, like this

=IFERROR(@INDEX('Sheet 2'!$F$3:$F$84,MATCH(0,IF(@'Sheet 2'!$C$3:$C$84<>3,1,IF(@'Sheet 2'!$A$3:$A$84<>B86,1,COUNTIF($C$12:C85,@'Sheet 2'!$F$3:$F$84)))),0),"")

Why is this happening? How do I stop this from happening?

Thanks in advance!

 

[DanteAmor]

Hi @excel12345u. Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.


In the formula you have in the VBA code you are missing a space in the first sheet name:

Also I think it should be an array formula:

  For i = lastRow + 1 To lastRow + count2 - count1
    criteriaSheet1.Range("C" & i).FormulaArray = _
      "=IFERROR(INDEX('Sheet 2'!$F$3:$F$" & lastRow & "," & _
          "MATCH(0,IF('Sheet 2'!$C$3:$C$" & lastRow & "<>3,1," & _
                  "IF('Sheet 2'!$A$3:$A$" & lastRow & "<>B" & i & ",1," & "COUNTIF($C$12:C" & i - 1 & "," & _
                     "'Sheet 2'!$F$3:$F$" & lastRow & ")))),0),"""")"
  Next i

I purposely arranged the lines like this to make it easier to read the formula and not the entire formula on one line.

Also, that way you can see which parts of the formula are similar and you can simplify them, like this:

  Dim colF As String, colC As String, colA As String
  
  For i = lastRow + 1 To lastRow + count2 - count1
    With Sheets("Sheet 2").Range("F3:F" & lastRow)
      colF = .Address(, , xlA1, True)
      colC = .Offset(0, -3).Address(, , xlA1, True)
      colA = .Offset(0, -5).Address(, , xlA1, True)
    End With
  
    criteriaSheet1.Range("C" & i).FormulaArray = _
      "=IFERROR(INDEX(" & colF & "," & _
          "MATCH(0,IF(" & colC & "<>3,1," & _
                  "IF(" & colA & "<>B" & i & ",1," & "COUNTIF($C$12:C" & i - 1 & "," & _
                     "" & colF & ")))),0),"""")"
  Next i

Or this way:

  For i = lastRow + 1 To lastRow + count2 - count1
    criteriaSheet1.Range("C" & i).FormulaArray = _
      Replace("=IFERROR(INDEX('Sheet 2'!$F$3:$F$#," & _
          "MATCH(0,IF('Sheet 2'!$C$3:$C$#<>3,1," & _
                  "IF('Sheet 2'!$A$3:$A$#<>B" & i & ",1," & "COUNTIF($C$12:C" & i - 1 & "," & _
                     "'Sheet 2'!$F$3:$F$#)))),0),"""")", "#", "" & lastRow & "")
  Next i

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[MARK858]

@excel12345u Try using

criteriaSheet1.Range("C" & i).Formula2

rather than

criteriaSheet1.Range("C" & i).Formula

You do need to look at your sheet names though as @DanteAmor has suggested