how to apply specific formula in selected range using vba

[Amit Modi]

hi ninja's

please explain me that how can I apply one specific formula in selected range I sheet using VBA.i am new to vba please provide sample code please.

example:: If [a1]+[b1]>5 then write text "ok" in range("c1:c20").

please help me.

please also provide if I want corresponding results I.e.( a1+b1)>5 then c1=ok
if (a2+b2)>5 then c2=ok
etc..

 

[Mike H..]

hi ninja's

please explain me that how can I apply one specific formula in selected range I sheet using VBA.i am new to vba please provide sample code please.

example:: If [a1]+[b1]>5 then write text "ok" in range("c1:c20").

please help me.

please also provide if I want corresponding results I.e.( a1+b1)>5 then c1=ok
if (a2+b2)>5 then c2=ok
etc..

Hi,

One way

Sub Values()
Dim c As Range
For Each c In Range("A1:A20")
If WorksheetFunction.Sum(c.Resize(, 2)) > 5 Then
  c.Offset(, 2) = "Ok"
End If
Next
End Sub

 

[Amit Modi]

thanks mike great yar

 

[Sathish KV]

Hi,

Also check out the below post

http://chandoo.org/forum/threads/creating-a-macro-to-adjust-in-row-number-change.18080/#post-109102

 

[Amit Modi]

mike is there any simple code?? I just want to understand as i am beginner.otherwise its great

 

[Amit Modi]

thanks sathish

 

[Mike H..]

mike is there any simple code?? I just want to understand as i am beginner.otherwise its great

Hi,

This one actually puts the formula on the worksheet.

Sub somesub()
With Range("C1")
  .Formula = "=if(a1+b1>5,""Ok"","""")"
  .Resize(20).FillDown
End With

 

[Amit Modi]

its exact for me. thanks guru