translating worksheet formula into VBA
 

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


--
dabith
------------------------------------------------------------------------
dabith's Profile: http://www.excelforum.com/member.php...o&userid=10566
View this thread: http://www.excelforum.com/showthread...hreadid=278086

translating worksheet formula into VBA
 

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


"dabith" schreef in bericht
...

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


--
dabith
------------------------------------------------------------------------
dabith's Profile:
http://www.excelforum.com/member.php...o&userid=10566
View this thread: http://www.excelforum.com/showthread...hreadid=278086

translating worksheet formula into VBA
 

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 :)
--
Dana DeLouis
Win XP & Office 2003


"dabith" wrote in message
...

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


--
dabith
------------------------------------------------------------------------
dabith's Profile:
http://www.excelforum.com/member.php...o&userid=10566
View this thread: http://www.excelforum.com/showthread...hreadid=278086