Help on formula string

[almecum]

Hi
I have the following code which is to copy the formula that is used in the last row of a column and copy down to the blank cells. The First FormualInvStr works just fine but the second one FormualStatStr gives me an error on how the formula is entered. I cannot see why the second one is not accepted as correct. I have but the variable between " & I & " which is fine on the first but not the second.

The finished formula should look like this =IF(O2="","",IF(O2>0,"file in ZIP","")) the formula works fine.

Public Sub CopyFormulaToBlankCells()
 Dim i As Long
 Dim FormulaInvStr As String
 Dim FormulaStatStr As String
 
    i = Range("O65536").End(xlUp).Row + 1
 
    FormulaInvStr = "=VALUE(TRIM(CLEAN(A" & i & ")))"
    ActiveSheet.Range("O:O").SpecialCells(xlCellTypeBlanks).Formula = FormulaInvStr
 
    FormulaStatStr = "=IF(O" & i & " ="","",IF(O" & i & ">0,"file in ZIP",""))"
    ActiveSheet.Range("P:P").SpecialCells(xlCellTypeBlanks).Formula = FormulaStatStr

 End Sub

 

[Rick Rothstein]

The finished formula should look like this =IF(O2="","",IF(O2>0,"file in ZIP","")) the formula works fine.

     FormulaStatStr = "=IF(O" & i & " ="","",IF(O" & i & ">0,"file in ZIP",""))"

You did not double up the quote marks that are internal to the Text string. Study my code below and compare it with yours...

FormulaStatStr = "=IF(O" & i & " ="""","""",IF(O2" & i & ">0,""file in ZIP"",""""))"