correct formula doesn't work after recording

Lenna

Active Member
Joined
Jun 25, 2014
Messages
269
Hello,

I'm trying to record a very long formula and copy it down to the end of data(row 1).
The formula works in Excel but I can't get to execute after I record it.

Here is my code, please help.

Thanks, Lenna

Code:
Columns("R:R").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("R1").Select
    ActiveCell.FormulaR1C1 = "OrderStatus"
    Range("R2").Select
    ActiveCell.FormulaR1C1 = _
        "=CHOOSE(IF(RC[-5]="""",1,2),IF(AND(RC[-1]<>""AT1R"",RC[-1]<>""LCTI"",RC[-1]<>""LPRI"",RC[-1]<>""LPRI_RPT"",RC[-1]<>""LPRII"",RC[-1]<>""LPRII_RPT"",RC[-1]<>""SABI_Dilution"",RC[-1]<>""SABII_Dilution"",RC[-1]<>""GP__I"",RC[-1]<>""GP__IDv2"",RC[-1]<>""GP__IDv1"",RC[-1]<>""GP__II"",RC[-1]<>""GP__MICA""),""NoOrderNumberFound"",""TestNotReported""),IF(ISERROR(VLOOKUP(RC[-" & _
        "1,FALSE)),""WrongTestOrdered"",""CorrectTest""))"
   
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("R2").Copy
    Range("R2:R" & FinalRow).Select
    ActiveSheet.Paste
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Something doesn't look right about the formula, especially the VLOOKUP towards the end which only seems to have 2 arguments.

Did you change anything in the code, or formula?

What is the correct formula as it would appear in a cell?

Actually when I add in the other arguments, using made-up values/ranges, for the VLOOKUP the formula works.

The part I added is in red in the code below.
Rich (BB code):
ActiveCell.FormulaR1C1 = _
        "=CHOOSE(IF(RC[-5]="""",1,2),IF(AND(RC[-1]<>""AT1R"",RC[-1]<>""LCTI"",RC[-1]<>""LPRI"",RC[-1]<>""LPRI_RPT"",RC[-1]<>""LPRII"",RC[-1]<>""LPRII_RPT"",RC[-1]<>""SABI_Dilution"",RC[-1]<>""SABII_Dilution"",RC[-1]<>""GP__I"",RC[-1]<>""GP__IDv2"",RC[-1]<>""GP__IDv1"",RC[-1]<>""GP__II"",RC[-1]<>""GP__MICA""),""NoOrderNumberFound"",""TestNotReported""),IF(ISERROR(VLOOKUP(RC[-" & _
        "1], C1:C5,5,FALSE)),""WrongTestOrdered"",""CorrectTest""))"
 
Upvote 0
Is the formula being cut off?

What does the whole formula look like?

Also, what happened to the missing arguments of the VLOOKUP?
 
Upvote 0
I've tried making the formula shorter and macro executed. This time I had relative references on, I'm not sure if that makes a difference?
Was my formula too long?

Here new code:

Code:
Columns("R:R").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("R1").Select
    ActiveCell.FormulaR1C1 = "OrderStatus"
    Range("R2").Select
    ActiveCell.FormulaR1C1 = _
        "=CHOOSE(IF(RC[-5]="""",1,2),IF(AND(RC[-1]<>""AT1R"",RC[-1]<>""LCTI"",RC[-1]<>""LPRI""),""NoOrderNumberFound"",""TestNotReported""),IF(ISERROR(VLOOKUP(RC[-1],C[1],1,FALSE)),""WrongTestOrdered"",""CorrectTest""))"
    'ActiveCell.Offset(1, 0).Range("A1").Select
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("R2").Copy
    Range("R2:R" & FinalRow).Select
    ActiveSheet.Paste
Something doesn't look right about the formula, especially the VLOOKUP towards the end which only seems to have 2 arguments.

Did you change anything in the code, or formula?

What is the correct formula as it would appear in a cell?

Actually when I add in the other arguments, using made-up values/ranges, for the VLOOKUP the formula works.

The part I added is in red in the code below.
Rich (BB code):
ActiveCell.FormulaR1C1 = _
        "=CHOOSE(IF(RC[-5]="""",1,2),IF(AND(RC[-1]<>""AT1R"",RC[-1]<>""LCTI"",RC[-1]<>""LPRI"",RC[-1]<>""LPRI_RPT"",RC[-1]<>""LPRII"",RC[-1]<>""LPRII_RPT"",RC[-1]<>""SABI_Dilution"",RC[-1]<>""SABII_Dilution"",RC[-1]<>""GP__I"",RC[-1]<>""GP__IDv2"",RC[-1]<>""GP__IDv1"",RC[-1]<>""GP__II"",RC[-1]<>""GP__MICA""),""NoOrderNumberFound"",""TestNotReported""),IF(ISERROR(VLOOKUP(RC[-" & _
        "1], C1:C5,5,FALSE)),""WrongTestOrdered"",""CorrectTest""))"
 
Upvote 0
I have no idea if the formula is too long because I don't know what your actual formula is, all I've got to work with is the code you've posted.

Mind you, I'm pretty sure your formula could be shortened, in particular the AND could probably be replaced with MATCH.
 
Upvote 0
ActiveCell.FormulaR1C1 = _
"=CHOOSE(IF(RC[-5]="""",1,2),IF(AND(RC[-1]<>""AT1R"",RC[-1]<>""LCTI"",RC[-1]<>""LPRI"",RC[-1]<>""LPRI_RPT"",RC[-1]<>""LPRII"",RC[-1]<>""LPRII_RPT"",RC[-1]<>""SABI_Dilution"",RC[-1]<>""SABII_Dilution"",RC[-1]<>""GP__I"",RC[-1]<>""GP__IDv2"",RC[-1]<>""GP__IDv1"",RC[-1]<>""GP__II"",RC[-1]<>""GP__MICA""),""NoOrderNumberFound"",""TestNotReported""),IF(ISERROR(VLOOKUP(RC[-" & _
"1,FALSE)),""WrongTestOrdered"",""CorrectTest""))"

Here is the original recorded formula... I don't think anything is missing. It returned a value in Excel but after I recorded it didn't work.

please help to figure out why?
Is the formula being cut off?

What does the whole formula look like?

Also, what happened to the missing arguments of the VLOOKUP?
 
Upvote 0
Code:
=CHOOSE(IF(M2="",1,2),IF(AND(Q2<>"AT1R",Q2<>"LCTI",Q2<>"LPRI",Q2<>"LPRII"),"NoOrderNumberFound","TestNotReported"),IF(ISERROR(VLOOKUP(Q2,S:S,1,FALSE)),"WrongTestOrdered","CorrectTest"))
Sorry,

Here is a shortened version of my formula. I remove some of Q2<>"" options to test, but would like to put them back once we figure out the problem.

Thanks,

Lenna
 
Upvote 0
Lenna

What is the actual formula you have on the worksheet?

Can you post that?
 
Upvote 0
Code:
=CHOOSE(IF(M2="",1,2),IF(AND(Q2<>"AT1R",Q2<>"LCTI",Q2<>"LPRI",Q2<>"LPRI_RPT",Q2<>"LPRII",Q2<>"LPRII_RPT",Q2<>"SABI_Dilution",Q2<>"SABII_Dilution",Q2<>"GP__I",Q2<>"GP__IDv2",Q2<>"GP__IDv1",Q2<>"GP__II",Q2<>"GP__MICA"),"NoOrderNumberFound","TestNotReported"),IF(ISERROR(VLOOKUP(Q2,S:S,1,FALSE)),"WrongTestOrdered","CorrectTest"))


Ok, Here is the original formula. Somehow, part of Vlookup argument was cut off while I was recording the formula(bug?):eek:
I've corrected the Vlookup argument and my macro executed.

Thanks,

Lenna

Lenna

What is the actual formula you have on the worksheet?

Can you post that?
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top