Replacing cells with \"\" with formula. When I run this, the formula leaves cell

Question

Replacing cells with "" with formula.

When I run this, the formula leaves cells with "" instead of actual blank cells. So, when I run it again, I get errors because there are technically no blank cells found - but cells with "".

Any suggestions?

Sub test()

'CHECKS RANGE FOR BLANK CELLS, REPLACES BLANKS WITH FORMULA

Range("G15:G1500").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=IF(IF(ISERROR(MOD(RC[-1]-((RC[-2]-INT(RC[-2]))),1)),"""",MOD(RC[-1]-((RC[-2]-INT(RC[-2]))),1))=R1C,"""",IF(ISERROR(MOD(RC[-1]-((RC[-2]-INT(RC[-2]))),1)),"""",MOD(RC[-1]-((RC[-2]-INT(RC[-2]))),1)))"
Range("G14").Select
'MsgBox ("Done!")

'REPLACES LIVE FORMULAS WITH JUST THE VALUES

Range("G15:G1500").Select

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("G14").Select

'GOES TO THE LAST ROW USED

Application.Run "TO_BOTTOM"
End Sub

Thanks !!

Explanation / Answer

Hi,

Try it this way :

Sub test()

'CHECKS RANGE FOR BLANK CELLS, REPLACES BLANKS WITH FORMULA

Range("G15:G1500").SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=IF(IF(ISERROR(MOD(RC[-1]-((RC[-2]-INT(RC[-2]))),1)),"""",MOD(RC[-1]-((RC[-2]-INT(RC[-2]))),1))=R1C,"""",IF(ISERROR(MOD(RC[-1]-((RC[-2]-INT(RC[-2]))),1)),"""",MOD(RC[-1]-((RC[-2]-INT(RC[-2]))),1)))"..

'MsgBox ("Done!")

'REPLACES LIVE FORMULAS WITH JUST THE VALUES

Range("G15:G1500").Value = Range("G15:G1500").Value

'GOES TO THE LAST ROW USED
Range("G14").Select
    Application.Run "TO_BOTTOM"
End Sub

Related Questions

Navigate

• Browse (All)
• Browse R
• Subjects