|
Macro line too long?
I went to program this formula in my macro, it did not work. I am thinking
that it is too long to accept it. I even did it manually while the macro was recording and it didn't record anything. Wonder why? The actual formula does work, but want it to work in my macro. Please help. ActiveCell.FormulaR1C1 = _ "=IF(ISERROR(RC[-1]),RC[2],INDEX('[Charge Type Translation.xls]Sheet1'!R2C1:R24C1,MAX(INDEX(('[Charge Type Translation.xls]Sheet1'!R2C2:R24C27=RC[3])*ROW('[Charge Type Translation.xls]Sheet1'!R2C2:R24C27),))-ROW('[Charge Type Translation.xls]Sheet1'!R1C2))" MrRJ |
Macro line too long?
Assuming your formula works, you just haven't broken it up correctly: ActiveCell.FormulaR1C1 = "=IF(ISERROR(RC[-1]),RC[2],INDEX" _ & "('[Charge Type Translation.xls]Sheet1'!R2C1:R24C1,MAX" _ & "(INDEX(('[Charge Type Translation.xls]Sheet1'!R2C2:R24C27=RC[3])*ROW" _ & "('[Charge Type Translation.xls]Sheet1'!R2C2:R24C27),))-ROW('[Charge Type Translation.xls]Sheet1'!R1C2))" MrRJ;236034 Wrote: I went to program this formula in my macro, it did not work. I am thinking that it is too long to accept it. I even did it manually while the macro was recording and it didn't record anything. Wonder why? The actual formula does work, but want it to work in my macro. Please help. ActiveCell.FormulaR1C1 = _ "=IF(ISERROR(RC[-1]),RC[2],INDEX('[Charge Type Translation.xls]Sheet1'!R2C1:R24C1,MAX(INDEX(('[Charge Type Translation.xls]Sheet1'!R2C2:R24C27=RC[3])*ROW('[Charge Type Translation.xls]Sheet1'!R2C2:R24C27),))-ROW('[Charge Type Translation.xls]Sheet1'!R1C2))" MrRJ -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=65874 |
Macro line too long?
Hi Simon,
I am still have problems. Not sure why? Range("P2").Select ActiveCell.FormulaR1C1 = _ "=IF(ISERROR(RC[-1]),RC[2],INDEX" _ & "('[Charge Type Translation.xls]Sheet1'!R2C1:R24C1,MAX" _ & "(INDEX(('[Charge Type Translation.xls]Sheet1'!R2C2:R24C27=RC[3])*ROW" _ & "('[Charge Type Translation.xls]Sheet1'!R2C2:R24C27),))-ROW('[Charge Type Translation.xls]Sheet1'!R1C2))" Range("P2").Select "Simon Lloyd" wrote: Assuming your formula works, you just haven't broken it up correctly: ActiveCell.FormulaR1C1 = "=IF(ISERROR(RC[-1]),RC[2],INDEX" _ & "('[Charge Type Translation.xls]Sheet1'!R2C1:R24C1,MAX" _ & "(INDEX(('[Charge Type Translation.xls]Sheet1'!R2C2:R24C27=RC[3])*ROW" _ & "('[Charge Type Translation.xls]Sheet1'!R2C2:R24C27),))-ROW('[Charge Type Translation.xls]Sheet1'!R1C2))" MrRJ;236034 Wrote: I went to program this formula in my macro, it did not work. I am thinking that it is too long to accept it. I even did it manually while the macro was recording and it didn't record anything. Wonder why? The actual formula does work, but want it to work in my macro. Please help. ActiveCell.FormulaR1C1 = _ "=IF(ISERROR(RC[-1]),RC[2],INDEX('[Charge Type Translation.xls]Sheet1'!R2C1:R24C1,MAX(INDEX(('[Charge Type Translation.xls]Sheet1'!R2C2:R24C27=RC[3])*ROW('[Charge Type Translation.xls]Sheet1'!R2C2:R24C27),))-ROW('[Charge Type Translation.xls]Sheet1'!R1C2))" MrRJ -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=65874 |
Macro line too long?
What error are you getting? MrRJ;236111 Wrote: Hi Simon, I am still have problems. Not sure why? Code: -------------------- Range("P2").Select ActiveCell.FormulaR1C1 = _ "=IF(ISERROR(RC[-1]),RC[2],INDEX" _ & "('[Charge Type Translation.xls]Sheet1'!R2C1:R24C1,MAX" _ & "(INDEX(('[Charge Type Translation.xls]Sheet1'!R2C2:R24C27=RC[3])*ROW" _ & "('[Charge Type Translation.xls]Sheet1'!R2C2:R24C27),))-ROW('[Charge Type Translation.xls]Sheet1'!R1C2))" Range("P2").Select -------------------- "Simon Lloyd" wrote: Assuming your formula works, you just haven't broken it up correctly: ActiveCell.FormulaR1C1 = "=IF(ISERROR(RC[-1]),RC[2],INDEX" _ & "('[Charge Type Translation.xls]Sheet1'!R2C1:R24C1,MAX" _ & "(INDEX(('[Charge Type Translation.xls]Sheet1'!R2C2:R24C27=RC[3])*ROW" _ & "('[Charge Type Translation.xls]Sheet1'!R2C2:R24C27),))-ROW('[Charge Type Translation.xls]Sheet1'!R1C2))" MrRJ;236034 Wrote: I went to program this formula in my macro, it did not work. I am thinking that it is too long to accept it. I even did it manually while the macro was recording and it didn't record anything. Wonder why? The actual formula does work, but want it to work in my macro. Please help. ActiveCell.FormulaR1C1 = _ "=IF(ISERROR(RC[-1]),RC[2],INDEX('[Charge Type Translation.xls]Sheet1'!R2C1:R24C1,MAX(INDEX(('[Charge Type Translation.xls]Sheet1'!R2C2:R24C27=RC[3])*ROW('[Charge Type Translation.xls]Sheet1'!R2C2:R24C27),))-ROW('[Charge Type Translation.xls]Sheet1'!R1C2))" MrRJ -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('The Code Cage' (http://www.thecodecage.com)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'Macro line too long? - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=65874) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=65874 |
| All times are GMT +1. The time now is 10:06 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com