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
|