Equation Replacement

[poleary2000]

I have the following equation:

=IF('Activity List'!H6>0,'Activity List'!H6,"")

It is copied down for 20k records. However, multiple rows were also manually deleted. There may have been 1k rows deleted. Thus, the equations look like this:

=IF('Activity List'!H6>0,'Activity List'!H6,"")
=IF('Activity List'!H6>0,'Activity List'!H7,"")
=IF('Activity List'!H6>0,'Activity List'!H10,"")
=IF('Activity List'!H6>0,'Activity List'!H11,"")

I need to change the equation to:

=IF(ISNUMBER('Activity List'!H6)=TRUE,'Activity List'!H6,"")

That is easy, but I can't just fill it down because of all the rows the person manually deleted. So, I'd like to replace the equation -- but can't figure out how to do it. Any thoughts?

 

[njimack]

Select the entire range that contains the formulae in question, and then run the following code (you might want to amend it to set calculation to manual)

Sub Replacement()
Dim r As Range

Application.ScreenUpdating = False

For Each r In Selection

    If r.HasFormula = True Then r.Formula = Replace(r.Formula, "'Activity List'!H6>0", "ISNUMBER('Activity List'!H6)=TRUE")

Next r

Application.ScreenUpdating = True

End Sub

 

[poleary2000]

That helps, but I should have been more clear. I need each equation to be different all the way down. Like:

=IF(ISNUMBER('Activity List'!H6)=TRUE,'Activity List'!H6,"")
=IF(ISNUMBER('Activity List'!H6)=TRUE,'Activity List'!H7,"")
=IF(ISNUMBER('Activity List'!H6)=TRUE,'Activity List'!H10,"")
=IF(ISNUMBER('Activity List'!H6)=TRUE,'Activity List'!H11,"")

Basically, whatever cell is being reference now with the bad equation...I need to be referenced with the good equation.

 

[shg]

Why not just find and replace

'Activity List'!H6>0

with

ISNUMBER('Activity List'!H6)

(The =TRUE part is redundant)

 

[poleary2000]

OK, didn't realize I didn't need the true. But how do I replace that for each different cell reference. That method works for one cell.

 

[njimack]

Did you try the code?
Actually, why not just use Find & Replace?

 

[poleary2000]

Did you try the code?
Actually, why not just use Find & Replace?

I tried the code. It replaces only the one cell with the new equation, because each subsequent cell might be H7 or h2091 or h21873.

Can you use Find and Replace to replace items on both sides of the H#? I can't figure out how to get rid of the >0 and also add the ISNUMBER prior.

Not sure if I am being clear enough here.

 

[poleary2000]

I need to convert this:

=IF('Activity List'!H6>0,'Activity List'!H6,"")
=IF('Activity List'!H7>0,'Activity List'!H7,"")
=IF('Activity List'!H10>0,'Activity List'!H10,"")
=IF('Activity List'!H11>0,'Activity List'!H11,"")


To this:

=IF(ISNUMBER('Activity List'!H6)=TRUE,'Activity List'!H6,"")
=IF(ISNUMBER('Activity List'!H7)=TRUE,'Activity List'!H7,"")
=IF(ISNUMBER('Activity List'!H10)=TRUE,'Activity List'!H10,"")
=IF(ISNUMBER('Activity List'!H11)=TRUE,'Activity List'!H11,"")

But there are more than 4 equations. This could go on for 20k records. Sorry for the confusion, I think I posted incorrectly above.

 

[shg]

Thus, the equations look like this:

=IF('Activity List'!H6>0,'Activity List'!H6,"")
=IF('Activity List'!H6>0,'Activity List'!H7,"")
=IF('Activity List'!H6>0,'Activity List'!H10,"")
=IF('Activity List'!H6>0,'Activity List'!H11,"")

That doesn't sound to me what the equations look like, then.

 

[poleary2000]

That doesn't sound to me what the equations look like, then.

Yes you are correct, I acknowledged my error above.