• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

macro to insert formula with if condition

S

Sushil

Member
Hi,
i am looking for a macro to insert formula in all of column H based on the condition that column G is not empty. formula will be if cell in column g is "DR" then value in cell corresponding in column H will be + of value in cell in column B, if it is "CR" in column G then it will be -ive of value in Cell of column b. i will appreciate if you can help me .

Thanks
Sushil
 
Try this

Code: 
Sub SMC1()
 
    Dim rng As Range
 
    For Each rng In Range("g1:g100") 'Adjust as necessary
        If UCASE(rng.Value) = "DR" Then
            Cells(rng.Row, "H").Value = Cells(rngRow,"B").Value
        ElseIf UCASE(rng.Value) = "CR" Then
            Cells(rng.Row, "H").Value = -Cells(rngRow,"B").Value
        End If
    Next rng
 
End Sub
 
sorry it does not run .. is it because if there is an empty cell in column G i.e there is no DR or Cr text in the cell
 
sorry clicked on post reply too quickly . I saw somewhere on other website that in case of text they use RC something
 
Hi Sushil ,

What you mean is probably the R1C1 style of addressing ; this by itself is not the issue ; the issue is to know the formula that you wish to insert using VBA.

If you can specify the formula , you should get an answer immediately.

I'll just hazard a guess ; please confirm or clarify.

=IF($G2 = "DR", $H2 , IF($G2 = "CR" , -$B2))

This does not specify what should be the output if G2 is neither "CR" nor "DR".

Please first confirm whether the above formula accurately reflects what you want ; if not , give the revised formula.

Thereafter , the correct formula can be implemented using VBA.

Narayan
 
Thanks Narayan for your reply. formula should be in column H =IF($G2 = "DR", $B2 , IF($G2 = "CR" , -$B2))
. but there will be some empty cells in the column which should be skipped or will show no value just blank cell. Also I would like to specify the range e.g G7 to G300. Thanks.
 
Hi Sushil ,

Try this :
Code: 
Public Sub Enter_Formula()
          Dim wks As Worksheet
          Set wks = ThisWorkbook.Worksheets("Sheet1")      '  Change as required
         
          Dim Rng As Range
          Set Rng = wks.Range("H7:H300")                    '  Change as required
         
          Rng.FormulaArray = [=IF($G7:$G300 = "DR", $B7:$G300, IF($G7:$G300 = "CR", -($B7:$B300),""))]
         
          Set Rng = Nothing
          Set wks = Nothing
End Sub

For more information , refer this link :

http://fastexcel.wordpress.com/2011...to-make-excels-evaluate-method-twice-as-fast/

Narayan
 
You must log in or register to reply here.
Back
Top