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.

 

[venkat1926]

not understood. any how suppose data is like this (formula in C2 is copied down )

Sheet1

*	A	B	C
hdng1	hdng2	hdng3
*
*
*
*
*
*
*	*	*
*	*	*
*
*	*	*
*
*	*	*
*	*	*
*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]14[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]18[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula C2 =IF(LEN(B2)=0,"",B2*2) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


try this macro (CAUTION: NOT ON ORIGINAL FILE BUT ON EXPERIMENTAL FILE FIRST AND THEN IF IT IS OK ON ORIGINAL FILE. BETTER KEEP ORIGINAL FILE SOMEWHERE FOR RETRIEVAL IF SOMETHING GOES WRONG.)

Sub test()
Dim r As Range, c As Range
Set r = Range(Range("B2"), Cells(Rows.Count, "B").End(xlUp))
For Each c In r
If c = 3 Then c.Offset(0, 1).Clear
Next c
End Sub

 

[hsandeep]

Instead of columns B, C, D you took the columns A, B, C. Studying....& would revert back.

 

[hsandeep]

venkat1926,

In your table: column A MUST generate +ve integers including 0 i.e. 0,1,2,3,4,5,...
For any appearance of 3 in a cell of column A (say A17=3), the corresponding column B's cell i.e B17 the formula should get removed from B17.
Please note: column B & column C contains formulas
I am using Excel 2007

 

[venkat1926]

your original message
"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)."

anyhow can you modify the macro to suit you

if problem post small extract of your data and explain again

 

[hsandeep]

I don't understand macros & hence unable to modify. Request you to please do the modifications.

Requirement: For 3 generated in any cell of column B, its corresponding cell address in column C must become FORMULA FREE.
Nothing to do with D column.
Here I have referred to my original post #1.

 

[venkat1926]

MY DATA INCLUDED A COLUMN WHICH IS BLANK COLUMN DONT WORRY ABOUT THAT
col. B has numbers and blanks. col. C has formula depending on col. B value of that row

the macro as it is does what you want. check it

click DEVELOPER click vb editor
hit control, +R
you see all open files. go to this file and click insert in menu bar and click module
in the window on the right side and copy this macro and save the file as macro enable excel file .xlsm

now click developer ribbon click "macros" you see test. highlight and click RUN on the right side.

 

[hsandeep]

It did something else:
It removed formula as well as the value making the cell as blank or "null". (WRONG)
It should only make it formula free; the VALUE SHOULD REMAIN BACK. (CORRECT).
In your table:
For B4 being equal to 3, it made C4=blank.(WRONG) I need D4 to become formula free ONLY & the value in D4 should remain back.
VERY IMP: Probably you have linked column D with column C which is again wrong. Column D is getting values from some other ws. It DOES NOT have any formula dependence on column C.
You may ask more questions without any doubt which I would feel happy to respond to.

 

[MARK858]

hsandeep, do you just mean you need the formula changed to values , something like...

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

by the way venkat1926 wasn't using column D at all.

By the way I am assuming that the 3 now appears in column B as you now seem to have reverted back to it from the post where it was in column A.

 

[hsandeep]

It WORKS MARK858. Thanks