Find & Replace macro with condition

[johncena]

Please Login or Register  to view this content.

Dear friends,at the moment I'm using above macro & i want to modify that with new conditions.my requirement is..

01.macro should check column "G" & column "G" has below values then according to that macro should replace values in column "L"

ex-
column "G"
TP
EMB
BHT

If column "G" has any of above values & same row column "L" like below,
column "L"
10
11
12

then macro should change that values like below..

Please Login or Register  to view this content.

02.If column "G" values other than above(TP,EMB,BHT) & same row column "L" like below,

column "L"
15
16.5
18

then macro should change that values like below..

Please Login or Register  to view this content.

pls help me to do this.thanks in advance..

[arlu1201]

Why dont you attach a sample file that you are using along with the macro? It will be easier for us to troubleshoot.

[johncena]

arlu1201@ my document is too big & lot of macro & formulas are there.so pls tell me unclear part in this macro..

[arlu1201]

You can use a dummy file with dummy data....

Your first code checks in column L and then changes column M right? It takes longer to provide a solution without a dummy file.

[johncena]

You can use a dummy file with dummy data....

Your first code checks in column L and then changes column M right? It takes longer to provide a solution without a dummy file.

Sorry for the late reply.here i have attached sample sheet..my macro not checking column "G" & it's just replacing column "L" & "M" values.my requirement is before replacing values macro should check column "G" & base on that it should replace column "L" & "M"values.

ex-
column "G"
TP
EMB
BHT

If column "G" has any of above values & same row column "L" like below,
column "L"
10
11
12

then macro should change that values like below..

Code:

Case 10: .Formula = "=5*1+5*1"
.Offset(0, 1).Value = .Offset(0, 1).Value & "A-B"
Case 11: .Formula = "=6*1+5*1"
.Offset(0, 1).Value = .Offset(0, 1).Value & "A-B"
Case 12: .Formula = "=6*1+6*1"
.Offset(0, 1).Value = .Offset(0, 1).Value & "A-B"


02.If column "G" values other than above(TP,EMB,BHT) & same row column "L" like below,

column "L"
15
16.5
18

then macro should change that values like below..

Code:

Case 15: .Formula = "=5*1.5+5*1.5"
.Offset(0, 1).Value = .Offset(0, 1).Value & "A-B"
Case 16.5: .Formula = "=6*1.5+5*1.5"
.Offset(0, 1).Value = .Offset(0, 1).Value & "A-B"
Case 18: .Formula = "=6*1.5+6*1.5"
.Offset(0, 1).Value = .Offset(0, 1).Value & "A-B"

pls check the sheet2,i have done it manually..so macro should do that..

[johncena]

pls help me.bump..

[watersev]

hi johncena,

Please Login or Register  to view this content.

[johncena]

watersev thanks a lot it's work. :D.REP+

[johncena]

watersev@ your macro work fine but it's replacing formula also..pls correct it..i think this part not working..thanks..
"If Not .HasFormula Then"

[pike]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found hereYour post does not comply with Rule 10 of our Forum RULES. Please acknowledge the response you receive, good or bad. If your problem is solved, please say so clearly.

[pike]

Johncena also remove your Quote to previous post!!!!

[watersev]

@Johncena, change this line:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

[johncena]

watersev@ what i want is if u replace any values in column "L" & "M" ,again no need to do any changes in same cell..i mean if i run your macro two time it's replacing same thing again & again.. but my macro not doing that..
ex- if u replace any value then it's become a formula..so that formula no need to replace..main problem is if i run 3 times then column "M" values change like this..154684A-BA-BA-B ,but it should only 154684A-B..

[johncena]

watersev@ thanks a lot now it's work.. :D

[watersev]

if you apply change to the code as per post #12 the code will check L column value for formula existence. If there is no formula, the code will insert one and add "A-B" to the next column same row value. If you run code again, the code will omit all cells in L column that have formulas in L so you will not get double "A-B" etc.

[pike]

Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore Moderators' or Administrators' requests - note that this includes requests by senior members as well, if you are unclear about their request or instruction then send a private message to them asking for help. Do not post a reply to a thread where a moderator has requested an action that has not been complied with e.g Title change or Code tags...etc