Excel Macro refuses to copy formula

[icouture]

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?

 

[chicagocomputerclasses]

What's in your B column on that tab?

 

[icouture]

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.

 

[icouture]

https://www.dropbox.com/s/ulibv44joknx3nh/Test%20excelquestion.xlsm?dl=0

Simply press the big button in the very first sheet.

All code is in module 6.

 

[mole999]

ADVERTISEMENT

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

 

[icouture]

=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.

 

[MARK858]

ADVERTISEMENT

Ignore the OP has just posted it

 

[MARK858]

I must admit I have had the same trouble as you with R1C1 for the formula but

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

 

[icouture]

I must admit I have had the same trouble as you with R1C1 for the formula but

 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.