translating worksheet formula into VBA

[dabith]

hey all

Could someone please help me with inserting a formula into VBA code?

My formula is :

= IF(C4="",A3,IF(ISNUMBER(VALUE(C4)),C4,A3))

I tried to translate it into my VBA but failed:

Range("A4").Select
ActiveCell.FormulaR1C1 =
"=if(RC[0,3]="",RC[-1,0],if(ISNUMBER(VALUE(RC[0,3])),RC[0,3],RC[-1,0]}}"

Any help appreciated.

Thanks

 

[Jos Vens]

Hi,

try this procedure to have all information on the activecell

Sub Info_Cell()

Dim vCell As Range
Dim vMsg As String

Set vCell = ActiveCell

vMsg = vCell.Address & " = >" & vCell.Value & "<" & vbCr
vMsg = vMsg & "Cell = >R" & vCell.Row & "C" & vCell.Column & "<" & vbCr
vMsg = vMsg & "Value = >" & vCell.Value & "<" & vbCr
vMsg = vMsg & "Formula = >" & vCell.Formula & "<" & vbCr
vMsg = vMsg & "FormulaR1C1 = >" & vCell.FormulaR1C1 & "<" & vbCr

MsgBox vMsg

End Sub

You can assign a userkey to this procedure and execute each time you want to
have the contents, formula or formulaR1C1

Greetings
Jos Vens

 

[Dana DeLouis]

Hello. The quotes within the string I believe needed to be double quotes.
Here are four options...

Range("A4").Formula = "=IF(C4="""",A3,IF(ISNUMBER(VALUE(C4)),C4,A3))"

Range("A4").Formula = "=IF(ISBLANK(C4),A3,IF(ISNUMBER(VALUE(C4)),C4,A3))"

Range("A4").FormulaR1C1 =
"=IF(RC[2]="""",R[-1]C,IF(ISNUMBER(VALUE(RC[2])),RC[2],R[-1]C))"

Range("A4").FormulaR1C1 =
"=IF(ISBLANK(RC[2]),R[-1]C,IF(ISNUMBER(VALUE(RC[2])),RC[2],R[-1]C))"

HTH :>)