VBA replaces formula but adds row in multiplier

B

bill ch

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
 
T

Tim

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
 
J

JLGWhiz

Tim, is there a typo in that last line?

Tim said:
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
 
B

Ben McBen

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")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top