Help needed for a macro

[hsandeep]

In 1 of the ws (out of 15) in my Excel workbook; B2:B200 generates +ve integers including 0 i.e. 0,1,2,3,4,5,... PLUS some cells in B2:B200 are blank (since no values/formulas being present there).

The corresponding C2:C200 & D2:D200 contains formulas & generates numerical values.
Output required: Formulas in corresponding C2:C200 SHOULD GET REMOVED IMMEDIATELY for corresponding B2:B200 generating 3.

Ex:
B2=1(thr' formula) C2=D2(formula) D2=200.32 (thr' formula)
B2
.
.
Now if:
B2=3 THE FORMULA IN C2 SHOULD GET REMOVED IMMEDIATELY

How to accomplish?
Thanks in advance.

 

[hsandeep]

MARK858 if you can help me.....
1st instance:
B8=2 C8=55.77 (formula =D8) D8=55.77 C8 CONTAINS FORMULA.
2nd instance:
B8=3 C8=57.90 D8=57.90 C8 becomes FORMULA FREE. The code is working up to this instance.
3rd instance:
B8=1 C8=88.77 (formula =D8) D8=88.77 C8 GETS BACK THE FORMULA.
4th instance:
B8=4 C8=66.66 D8=66.66 C8 becomes FORMULA FREE.
& so on....

Please note:
1. column B will always contain either of 1,2,3,4,5 PLUS some cells in column B are blank (since no values/formulas being present there).
2. column C by default is loaded with formula =D ALWAYS.
3. There is always column D.

Please help

 

[MARK858]

You are removing the formula in the second instance when you change it to a value, once you have removed a formula it is gone.

If you need to have the formula back in the cell then you need to re-insert either via code or manually

 

[hsandeep]

If you need to have the formula back in the cell then you need to re-insert either via code or manually[/QUOTE]
Please re-insert the formula in the column C as & when a value other than 3 i.e. 1,2,4,5 appears in the column C via code. The formula in the column C IS ALWAYS '=D' function. Ex: C48=D48 & so on.......Please do help.

 

[MARK858]

Turn on the macro recorder and record yourself putting the formula in cell C2

You already have a range for the cells in column B and so offset it by 1 column, add .FormulaR1C1= then add the formula the macro recorder produced at the end.

then place the line after where the range is defined in the code.

 

[hsandeep]

MARK858

I can understand that I would learn macro by doing above. But I am very novice in macros & would deeply appreciate if you can make the macros for me. I would be using it immediately for tomorrow's business.... Please understand my requirement..& do the needful brother.

 

[MARK858]

I can understand that I would learn macro by doing above.

Exactly...
Post what you you come up with following the instructions in post #14 then I will correct it if needed.

 

[hsandeep]

I know where the Macro Recorder button is & have seen someone few years back doing 'macro recording'. I won't run away from doing this exercise but please guide few lines for initial movements...

 

[MARK858]

hsandeep, you only use the macro recorder for getting the correct syntax for the formula then you stop it.
the formula it gives you is "=RC[1]"

Your range in column B is called r
and you offset it by 1 column
r.offset(0,1)
then add .FormulaR1C1
r.Offset(0,1).FormulaR1C1
then add the formula
r.Offset(, 1).FormulaR1C1 = "=RC[1]"

and add it to the code

Sub test()
    Dim r As Range, c As Range
    Set r = Range(Range("B2"), Cells(Rows.Count, "B").End(xlUp))
    [COLOR="#FF0000"]r.Offset(0, 1).FormulaR1C1 = "=RC[1]"[/COLOR]
    For Each c In r
        If c.Value = 3 Then c.Offset(0, 1).Value = c.Offset(0, 1).Value
    Next c
End Sub

Really with 500+ posts you should be picking up the basics and attempting some yourself by now (especially how to record code).

 

[hsandeep]

Really with 500+ posts you should be picking up the basics and attempting some yourself by now (especially how to record code).[/QUOTE]

Second BEST response received after becoming a member of this superb site. Thanks a lot to you. I am going to try this till tuesday next in real time feeds & report you back instantly about the outcome.

 

[hsandeep]

Reporting you from my live desk:
The code is not working..Some cells of column B has values 3 & even then the corresponding column C's formula is not becoming FORMULA FREE. column C takes values from column D which in turn gets values from a column which is real time feed.

Any help at this (hasty) moment?