Excel Macro refuses to copy formula

icouture

New Member
Joined
Nov 1, 2015
Messages
9
I am at a loss right now. I made a macro to format some tables to insert into our ERP system. It copies formulas in quite a few places and all works fine, except this one.

I get a bug whereas my stops completely after this line:


Sheets("Transfert vers Epicor").Select
ActiveSheet.Range("I8").Select
Range("I8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNUMBER(SEARCH(""pliage"",RC[-5],9)),VLOOKUP(RC[-4],Fournisseurs!R1C[-8]:R10C[-5],3,FALSE),IF(ISNUMBER(SEARCH(""Decoupe*"",RC[-5])),VLOOKUP(RC[-4],Fournisseurs!R1C[-8]:R10C[-5],2,FALSE),IF(ISNUMBER(SEARCH(""Coupe"",RC[-5])),VLOOKUP(RC[-4],Fournisseurs!R1C[-8]:R10C[-5],2,FALSE),IF(ISNUMBER(SEARCH(""Usinage"",RC[-5])),VLOOKUP(RC[-4],Fournisseurs!R1C[-8]:R10C[-5]," & _
",""""))))"

Range("I8").Select
Selection.AutoFill Destination:=Range("I8:I" & Cells(Rows.Count, "B").End(xlUp).Row)


I cannot seem to understand why, it copies formulas fine with that same structure up until it gets to this point.


Am I missing something?
 
Last edited:

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Part numbers, I used it to count the number of times I need to do each operation as this dictates pretty much everything.

I can include the file "ready to go" should you want to test anything.
 
Upvote 0
when I tried to copy that formula to the I8 cell, it was truncated at the line wrap, then it complained about the double quotes, and then the RC notation, so possibly a few areas of contention
 
Upvote 0
=SI(ESTNUM(CHERCHE("pliage";D8;9));RECHERCHEV(E8;Fournisseurs!A$1:D$10;3;FAUX);SI(ESTNUM(CHERCHE("Decoupe*";D8));RECHERCHEV(E8;Fournisseurs!A$1:D$10;2;FAUX);SI(ESTNUM(CHERCHE("Coupe";D8));RECHERCHEV(E8;Fournisseurs!A$1:D$10;2;FAUX);SI(ESTNUM(CHERCHE("Usinage";D8));RECHERCHEV(E8;Fournisseurs!A$1:D$10;2;FAUX);""))))

This I what I entered, I used the record macro feature to translate it into VBA. It works when I copy it in manually, but VBA refuses to copy it.
 
Upvote 0
Ignore the OP has just posted it :biggrin:
 
Last edited:
Upvote 0
I must admit I have had the same trouble as you with R1C1 for the formula but

Code:
 Sheets("Transfert vers Epicor").Range("I8:I" & Sheets("Transfert vers Epicor").Cells(Rows.Count, "B").End(xlUp).Row).Formula = "=IF(ISNUMBER(SEARCH(""pliage"",D8,9)),VLOOKUP(E8,Fournisseurs!A$1:D$10,3,FALSE),IF(ISNUMBER(SEARCH(""Decoupe*"",D8)),VLOOKUP(E8,Fournisseurs!A$1:D$10,2,FALSE),IF(ISNUMBER(SEARCH(""Coupe"",D8)),VLOOKUP(E8,Fournisseurs!A$1:D$10,2,FALSE),IF(ISNUMBER(SEARCH(""Usinage"",D8)),VLOOKUP(E8,Fournisseurs!A$1:D$10,2,FALSE),""""))))"

seems to work for me (the above replaces all your previous code).
 
Upvote 0
I must admit I have had the same trouble as you with R1C1 for the formula but

Code:
 Sheets("Transfert vers Epicor").Range("I8:I" & Sheets("Transfert vers Epicor").Cells(Rows.Count, "B").End(xlUp).Row).Formula = "=IF(ISNUMBER(SEARCH(""pliage"",D8,9)),VLOOKUP(E8,Fournisseurs!A$1:D$10,3,FALSE),IF(ISNUMBER(SEARCH(""Decoupe*"",D8)),VLOOKUP(E8,Fournisseurs!A$1:D$10,2,FALSE),IF(ISNUMBER(SEARCH(""Coupe"",D8)),VLOOKUP(E8,Fournisseurs!A$1:D$10,2,FALSE),IF(ISNUMBER(SEARCH(""Usinage"",D8)),VLOOKUP(E8,Fournisseurs!A$1:D$10,2,FALSE),""""))))"

seems to work for me (the above replaces all your previous code).

Thank you so very much. This works perfectly!

Will likely try and retrofit this into previous code to simplify.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

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