Copying formulas rather than values

M

Macro

I created the below macro,

Sub Expense_Code()
'
' Expense_Code Macro
' Macro recorded 20/08/2004 by GNER
'
' Keyboard Shortcut: Ctrl+Shift+D
'
Columns("J:J").Select
Selection.Insert Shift:=xlToRight
Range("J1").Select
ActiveCell.FormulaR1C1 = "G/L Description"
Range("J2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[Suplocup.xls]Expense Codes'!
R1C1:R24C3,2,0)"
Range("J3").Select
Selection.CurrentRegion.Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
End Sub

and the last section selects the blank cells and then copy
the data in the top cell into all the blank cells, which
is fine.

But the data in the top cell is a formula, and after it's
being copied into the blanks, it turns into a value.

How can i get it to copy down as a formula.

Many Thanks

Martyn
 
B

Bernie Deitrick

Martyn,

Change

Selection.FormulaR1C1 = "=R[-1]C"

To

Dim myCell As Range

For Each myCell In Selection
If myCell(0, 1).HasFormula Then
myCell(0, 1).Copy myCell
Else
myCell.Value = myCell(0, 1).Value
'or use
'myCell.FormulaR1C1 = "=R[-1]C"
End If
Next myCell

HTH,
Bernie
MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top