Macro line too long?

[MrRJ]

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]

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 Typ
Translation.xls]Sheet1'!R2C2:R24C27=RC[3])*ROW"
& "('[Charge Type Translation.xls]Sheet1'!R2C2:R24C27),))-ROW('[Charg
Type Translation.xls]Sheet1'!R1C2))

I went to program this formula in my macro, it did not work. I a
thinkin
that it is too long to accept it. I even did it manually while th
macro wa
recording and it didn't record anything. Wonder why? The actual formul
does work, but want it to work in my macro

Please help
ActiveCell.FormulaR1C1 =
"=IF(ISERROR(RC[-1]),RC[2],INDEX('[Charge Typ
Translation.xls]Sheet1'!R2C1:R24C1,MAX(INDEX(('[Charge Typ
Translation.xls]Sheet1'!R2C2:R24C27=RC[3])*ROW('[Charge Typ
Translation.xls]Sheet1'!R2C2:R24C27),))-ROW('[Charge Typ
Translation.xls]Sheet1'!R1C2))

MrR

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com

 

[MrRJ]

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

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

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]

What error are you getting

Hi Simon
I am still have problems. Not sure why Code
-------------------Range("P2").Selec
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('[Charg
Type Translation.xls]Sheet1'!R1C2))
Range("P2").Selec
-------------------

Assuming your formula works, you just haven't broken it u correctly
ActiveCell.FormulaR1C1
"=IF(ISERROR(RC[-1]),RC[2],INDEX"
& "('[Charge Type Translation.xls]Sheet1'!R2C1:R24C1,MAX"
& "(INDEX(('[Charge Typ
Translation.xls]Sheet1'!R2C2:R24C27=RC[3])*ROW"
& "('[Charge Typ Translation.xls]Sheet1'!R2C2:R24C27),))-ROW('[Charg
Type Translation.xls]Sheet1'!R1C2))

MrRJ;236034 Wrote

I went to program this formula in my macro, it did not work. I a
thinkin
that it is too long to accept it. I even did it manually whil th
macro wa
recording and it didn't record anything. Wonder why? The actua formul
does work, but want it to work in my macro

Please help
ActiveCell.FormulaR1C1 =
"=IF(ISERROR(RC[-1]),RC[2],INDEX('[Charge Typ
Translation.xls]Sheet1'!R2C1:R24C1,MAX(INDEX(('[Charge Typ
Translation.xls]Sheet1'!R2C2:R24C27=RC[3])*ROW('[Charge Typ
Translation.xls]Sheet1'!R2C2:R24C27),))-ROW('[Charge Typ
Translation.xls]Sheet1'!R1C2))

MrR

-
Simon Lloy

Regards
Simon Lloy
'The Code Cage' ('The Code Cage' (http://www.thecodecage.com)

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/showthread.php?t=65874

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com