| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA replaces formula but adds row in multiplier
The formula in cell V2 is:
=IF(BB2="SURG",(IF(H2<13,"No Outlier",(H2-13)*5128)),"Go to Med") The VBA below, copies the formula down into other cells but it replaces the multiplier of 5128 by inserting the row number into it. For example if copied to row 81 then the 5128 becomes 51818. It apparently is replacing all 2's with the new corresponding row number. Is there a way to fix this VBA so it only changes the cell values in the formula and not the multiplier like so: =IF(BB81="SURG",(IF(H81<13,"No Outlier",(H81-13)*5128)),"Go to Med") For x = 21 To 2500 If Cells(x, 2).Text = "J" And _ Cells(x, 22).Text = "Go to surg" Or _ Cells(x, 2).Text = "J" And _ Cells(x, 22).Text = "#N/A" Then Cells(x, 22).Formula = Replace(Cells(2, 22).Formula, 2, x) flag = 1 End If Next Calculate End Sub Thanks for your help, -Bill |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA replaces formula but adds row in multiplier
s=Cells(2, 22).Formula
s=replace(s, "BB2","BB" & x) s=replace(s, "H2","H" & x) Cells(x, 22).Formula = x ...or just copy/paste the formula in code. You might also consider making your "If ... Then" criteria more explicit: ie. not A and B or C and D but (A and B) or (C and D) Easier to follow, and no chance of unexpected results. Tim "bill ch" wrote in message ... The formula in cell V2 is: =IF(BB2="SURG",(IF(H2<13,"No Outlier",(H2-13)*5128)),"Go to Med") The VBA below, copies the formula down into other cells but it replaces the multiplier of 5128 by inserting the row number into it. For example if copied to row 81 then the 5128 becomes 51818. It apparently is replacing all 2's with the new corresponding row number. Is there a way to fix this VBA so it only changes the cell values in the formula and not the multiplier like so: =IF(BB81="SURG",(IF(H81<13,"No Outlier",(H81-13)*5128)),"Go to Med") For x = 21 To 2500 If Cells(x, 2).Text = "J" And _ Cells(x, 22).Text = "Go to surg" Or _ Cells(x, 2).Text = "J" And _ Cells(x, 22).Text = "#N/A" Then Cells(x, 22).Formula = Replace(Cells(2, 22).Formula, 2, x) flag = 1 End If Next Calculate End Sub Thanks for your help, -Bill |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA replaces formula but adds row in multiplier
Tim, is there a typo in that last line?
"Tim" wrote: s=Cells(2, 22).Formula s=replace(s, "BB2","BB" & x) s=replace(s, "H2","H" & x) Cells(x, 22).Formula = x ...or just copy/paste the formula in code. You might also consider making your "If ... Then" criteria more explicit: ie. not A and B or C and D but (A and B) or (C and D) Easier to follow, and no chance of unexpected results. Tim "bill ch" wrote in message ... The formula in cell V2 is: =IF(BB2="SURG",(IF(H2<13,"No Outlier",(H2-13)*5128)),"Go to Med") The VBA below, copies the formula down into other cells but it replaces the multiplier of 5128 by inserting the row number into it. For example if copied to row 81 then the 5128 becomes 51818. It apparently is replacing all 2's with the new corresponding row number. Is there a way to fix this VBA so it only changes the cell values in the formula and not the multiplier like so: =IF(BB81="SURG",(IF(H81<13,"No Outlier",(H81-13)*5128)),"Go to Med") For x = 21 To 2500 If Cells(x, 2).Text = "J" And _ Cells(x, 22).Text = "Go to surg" Or _ Cells(x, 2).Text = "J" And _ Cells(x, 22).Text = "#N/A" Then Cells(x, 22).Formula = Replace(Cells(2, 22).Formula, 2, x) flag = 1 End If Next Calculate End Sub Thanks for your help, -Bill |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA replaces formula but adds row in multiplier
Yes, should be "s":
Cells(x, 22).Formula = s Thanks Tim "JLGWhiz" wrote in message ... Tim, is there a typo in that last line? "Tim" wrote: s=Cells(2, 22).Formula s=replace(s, "BB2","BB" & x) s=replace(s, "H2","H" & x) Cells(x, 22).Formula = x ...or just copy/paste the formula in code. You might also consider making your "If ... Then" criteria more explicit: ie. not A and B or C and D but (A and B) or (C and D) Easier to follow, and no chance of unexpected results. Tim "bill ch" wrote in message ... The formula in cell V2 is: =IF(BB2="SURG",(IF(H2<13,"No Outlier",(H2-13)*5128)),"Go to Med") The VBA below, copies the formula down into other cells but it replaces the multiplier of 5128 by inserting the row number into it. For example if copied to row 81 then the 5128 becomes 51818. It apparently is replacing all 2's with the new corresponding row number. Is there a way to fix this VBA so it only changes the cell values in the formula and not the multiplier like so: =IF(BB81="SURG",(IF(H81<13,"No Outlier",(H81-13)*5128)),"Go to Med") For x = 21 To 2500 If Cells(x, 2).Text = "J" And _ Cells(x, 22).Text = "Go to surg" Or _ Cells(x, 2).Text = "J" And _ Cells(x, 22).Text = "#N/A" Then Cells(x, 22).Formula = Replace(Cells(2, 22).Formula, 2, x) flag = 1 End If Next Calculate End Sub Thanks for your help, -Bill |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA replaces formula but adds row in multiplier
Yeah, that makes more sense.<g
"Tim" wrote: Yes, should be "s": Cells(x, 22).Formula = s Thanks Tim "JLGWhiz" wrote in message ... Tim, is there a typo in that last line? "Tim" wrote: s=Cells(2, 22).Formula s=replace(s, "BB2","BB" & x) s=replace(s, "H2","H" & x) Cells(x, 22).Formula = x ...or just copy/paste the formula in code. You might also consider making your "If ... Then" criteria more explicit: ie. not A and B or C and D but (A and B) or (C and D) Easier to follow, and no chance of unexpected results. Tim "bill ch" wrote in message ... The formula in cell V2 is: =IF(BB2="SURG",(IF(H2<13,"No Outlier",(H2-13)*5128)),"Go to Med") The VBA below, copies the formula down into other cells but it replaces the multiplier of 5128 by inserting the row number into it. For example if copied to row 81 then the 5128 becomes 51818. It apparently is replacing all 2's with the new corresponding row number. Is there a way to fix this VBA so it only changes the cell values in the formula and not the multiplier like so: =IF(BB81="SURG",(IF(H81<13,"No Outlier",(H81-13)*5128)),"Go to Med") For x = 21 To 2500 If Cells(x, 2).Text = "J" And _ Cells(x, 22).Text = "Go to surg" Or _ Cells(x, 2).Text = "J" And _ Cells(x, 22).Text = "#N/A" Then Cells(x, 22).Formula = Replace(Cells(2, 22).Formula, 2, x) flag = 1 End If Next Calculate End Sub Thanks for your help, -Bill |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA replaces formula but adds row in multiplier
Dont know about the context of this issue - but might it not be easier just
to make the formula itself do the work: =IF(BB2="SURG",(IF(H2<13,"No Outlier",(H2-13)*VALUE("51"&ROW() &"8"))),"Go to Med") "bill ch" wrote: The formula in cell V2 is: =IF(BB2="SURG",(IF(H2<13,"No Outlier",(H2-13)*5128)),"Go to Med") The VBA below, copies the formula down into other cells but it replaces the multiplier of 5128 by inserting the row number into it. For example if copied to row 81 then the 5128 becomes 51818. It apparently is replacing all 2's with the new corresponding row number. Is there a way to fix this VBA so it only changes the cell values in the formula and not the multiplier like so: =IF(BB81="SURG",(IF(H81<13,"No Outlier",(H81-13)*5128)),"Go to Med") For x = 21 To 2500 If Cells(x, 2).Text = "J" And _ Cells(x, 22).Text = "Go to surg" Or _ Cells(x, 2).Text = "J" And _ Cells(x, 22).Text = "#N/A" Then Cells(x, 22).Formula = Replace(Cells(2, 22).Formula, 2, x) flag = 1 End If Next Calculate End Sub Thanks for your help, -Bill |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA replaces formula but adds row in multiplier
This works great! Thanks for the education.
-Bill "Tim" wrote: Yes, should be "s": Cells(x, 22).Formula = s Thanks Tim "JLGWhiz" wrote in message ... Tim, is there a typo in that last line? "Tim" wrote: s=Cells(2, 22).Formula s=replace(s, "BB2","BB" & x) s=replace(s, "H2","H" & x) Cells(x, 22).Formula = x ...or just copy/paste the formula in code. You might also consider making your "If ... Then" criteria more explicit: ie. not A and B or C and D but (A and B) or (C and D) Easier to follow, and no chance of unexpected results. Tim "bill ch" wrote in message ... The formula in cell V2 is: =IF(BB2="SURG",(IF(H2<13,"No Outlier",(H2-13)*5128)),"Go to Med") The VBA below, copies the formula down into other cells but it replaces the multiplier of 5128 by inserting the row number into it. For example if copied to row 81 then the 5128 becomes 51818. It apparently is replacing all 2's with the new corresponding row number. Is there a way to fix this VBA so it only changes the cell values in the formula and not the multiplier like so: =IF(BB81="SURG",(IF(H81<13,"No Outlier",(H81-13)*5128)),"Go to Med") For x = 21 To 2500 If Cells(x, 2).Text = "J" And _ Cells(x, 22).Text = "Go to surg" Or _ Cells(x, 2).Text = "J" And _ Cells(x, 22).Text = "#N/A" Then Cells(x, 22).Formula = Replace(Cells(2, 22).Formula, 2, x) flag = 1 End If Next Calculate End Sub Thanks for your help, -Bill |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| replaces formula to its value | Excel Worksheet Functions | |||
| what is the formula that identifies v8 in a row & adds v8+v8=v16? | Excel Discussion (Misc queries) | |||
| #Value replaces formula result when file is opened. But why? | Excel Discussion (Misc queries) | |||
| How to set up a formula that it adds value on a certain date | Excel Discussion (Misc queries) | |||
| how to create a repeat formula using the same multiplier? | Excel Worksheet Functions | |||
Linear Mode
