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.
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.
Code:
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