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:

icouture

New Member
Joined
Nov 1, 2015
Messages
9
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.
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
9,955
Office Version
2019, 2016, 2013
Platform
Windows
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

New Member
Joined
Nov 1, 2015
Messages
9
=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

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,751
Office Version
365, 2010
Platform
Windows, Mobile
Ignore the OP has just posted it :biggrin:
 
Last edited:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,751
Office Version
365, 2010
Platform
Windows, Mobile
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).
 

icouture

New Member
Joined
Nov 1, 2015
Messages
9
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.
 

Forum statistics

Threads
1,085,870
Messages
5,386,479
Members
402,000
Latest member
CROSBIE3103

Some videos you may like

This Week's Hot Topics

Top