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?

 

[shg]

Then try this:

Replace = with |=

Replace ('Act with (ISNUMBER('Act

Replace >0 with )

Replace |= with =

 

[poleary2000]

Then try this:

Replace = with |=

Replace ('Act with (ISNUMBER('Act

Replace >0 with )

Replace |= with =

GREAT IDEA. However, I get bogged down on the last step. It won't find anything for me.

 

[poleary2000]

GREAT IDEA. However, I get bogged down on the last step. It won't find anything for me.

Nevermind. I am a dummy. This worked PERFECTLY. Thanks!

 

[njimack]

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.

I know you've got your solution, but the coode was designed to run on every cell in your selection, and it would have done exactly what you asked for.

 

[poleary2000]

I did try to code, but it only worked for the cell that had the reference H6. My original post did not include the correct information. It needed to be dynamic to know what the reference was and keep it. And I couldn't figure out how to edit the code in order to do that.