# correct formula doesn't work after recording

#### Lenna

##### Active Member
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.

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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### Norie

##### Well-known Member
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""))"``````

#### Lenna

##### Active Member
Is it too long? How long is too long?

#### Norie

##### Well-known Member
Is the formula being cut off?

What does the whole formula look like?

Also, what happened to the missing arguments of the VLOOKUP?

#### Lenna

##### Active Member
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,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""))"``````

#### Norie

##### Well-known Member
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.

#### Lenna

##### Active Member
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.

Is the formula being cut off?

What does the whole formula look like?

Also, what happened to the missing arguments of the VLOOKUP?

#### Lenna

##### Active Member
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

#### Norie

##### Well-known Member
Lenna

What is the actual formula you have on the worksheet?

Can you post that?

#### Lenna

##### Active Member
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?) 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?

Replies
12
Views
853
Replies
7
Views
741
Replies
2
Views
1K

### Forum statistics

1,191,287
Messages
5,985,760
Members
439,981
Latest member
ofori francis ### 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.

### Which adblocker are you using?    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

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