Help needed for a macro

V

venkat1926

Well-known Member

Joined

Aug 21, 2005

Messages

4,824

• Sep 14, 2014

• #2

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

Sheet1

*	A	B	C
1	hdng1	hdng2	hdng3
2	*	1	2
3	*	2	4
4	*	3	6
5	*	4	8
6	*	3	6
7	*	2	4
8	*	*	*
9	*	*	*
10	*	5	10
11	*	*	*
12	*	7	14
13	*	*	*
14	*	*	*
15	*	9	18

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</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.)

Code:

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

 

Upvote 0

H

hsandeep

Well-known Member

Joined

Dec 6, 2008

Messages

1,213

Office Version

1. 2010

Platform

1. Windows
2. Mobile

• Sep 14, 2014

• #3

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

 

Upvote 0

H

hsandeep

Well-known Member

Joined

Dec 6, 2008

Messages

1,213

Office Version

1. 2010

Platform

1. Windows
2. Mobile

• Sep 14, 2014

• #4

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

 

Last edited: Sep 14, 2014

Upvote 0

V

venkat1926

Well-known Member

Joined

Aug 21, 2005

Messages

4,824

• Sep 14, 2014

• #5

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

 

Upvote 0

H

hsandeep

Well-known Member

Joined

Dec 6, 2008

Messages

1,213

Office Version

1. 2010

Platform

1. Windows
2. Mobile

• Sep 14, 2014

• #6

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.

 

Last edited: Sep 14, 2014

Upvote 0

V

venkat1926

Well-known Member

Joined

Aug 21, 2005

Messages

4,824

• Sep 14, 2014

• #7

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.

 

Upvote 0

H

hsandeep

Well-known Member

Joined

Dec 6, 2008

Messages

1,213

Office Version

1. 2010

Platform

1. Windows
2. Mobile

• Sep 14, 2014

• #8

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.

 

Upvote 0

MARK858

MrExcel MVP

Joined

Nov 12, 2010

Messages

16,164

Office Version

1. 365

Platform

1. Windows
2. Mobile

• Sep 14, 2014

• #9

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

Code:

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.

 

Last edited: Sep 14, 2014

Upvote 0

H

hsandeep

Well-known Member

Joined

Dec 6, 2008

Messages

1,213

Office Version

1. 2010

Platform

1. Windows
2. Mobile

• Sep 14, 2014

• #10

It WORKS MARK858. Thanks

 

Upvote 0