Need Formula Edit

Question

Excel 2003 ... I do not know Code ... I record Macros only (then sometimes I do some creative cut/paste) ... Issue is ... I recorded placing a Formula in WS1 Cell O2 (doing this from memory, but think I have it) ... Anyway, the following formula (as recorded code) worked fine until I inserted a new Col K in WS2.

ActiveCell.FormulaR1C1 = _

"=IF(ISNA(INDEX(WS2!R1C15:R2000C15,MATCH(RC[-10],WS2!R1C12:R2000C12,0))),"""",INDEX(WS2!R1C15:R2000C15,MA

TCH(RC[-10],WS2!R1C12:R2000C12,0)))"

Now whenever I run the Macro containing the above a Window opens up when I hit this instruction.  I tried changing all the 15's to 16 & the 12's to 13, but no luck.  I do not know how to properly edit the R1C1 Format in the Macro???

How do you do this ... or ... should I just re-record this portion? ... Thanks ... Kha

Answer 1

Try changing -10 to -11.

R1C15 means cell O1. When you inserted it will become P1 so you should use R1C16.

Similarly R1C12 was L which has become P so C12 should move to C13. You got this part right.

Now Match RC[-10] would have takend you to Col E on the sheet containing the formula. Is that what you want? If not then you will have to adjust -10 accordingly.

You are right though, it might be a good idea to record the macro again and then compare what has changed so that you understand it better.

Answer 2

Due to the short-comings on my part ... I decided to stage the formula I needed in an outside cell ... & then use recorded Macro to copy/paste formula where I needed it rather than attempt to rerecord formula in Macro ...

This post helped me decide what to do ... Thanks for supporting these boards ... Kha

Answer 3

Did you try out my suggestion?

If you change the name of a sheet, formulae pointing to that sheet get updated. The name does not change in recoreded macros...

Can you share your file? You may upload to wikisend.com and paste the link here or mail it to me. Do remove any confidential information.

Answer 4

New Info ... I also changed the NAME of the WS appearing in the INDEX Formula ... Then I changed the WS Name in the recorded Formula ... However, recorded Formula (while it looks ok) still appears to be pointing to the Original WS Name ... (ie:  similar to when you copy a Formula to a different WB, but the Formula stays linked to the Orginal WB)

Ok ... guess I am going to re-record, but would still like to know how to edit so the recorded Formula points to the correct WS after I change the WS Name.

Thanks ... Kha