CODEWITHSUNDEEP
vbaexcel
user2438237
user2438237

Reputation: 315

Compilation error for a formula in VBA

I am trying to use a formula which extracts data from one column and populates other column. However my formula is working fine; but when I am trying to use it in VB script, it is throwing error. Please help me out

Formula

=IF(ISERR(MID(I7,FIND(" ",I7,FIND("Country:",I7))+1,IF(
  ISERR(
   FIND("Country:",I7,1)),0,
   FIND("Process:",I7,1)) - IF(ISERR(FIND("Country:",I7,1)),0,FIND(" ",I7,FIND("Country:",I7))+1))),"UNDEFINED",MID(I7,FIND(" ",I7,FIND("Country:",I7))+1,IF(
  ISERR(
   FIND("Country:",I7,1)),0,
   FIND("Process:",I7,1)) - IF(ISERR(FIND("Country:",I7,1)),0,FIND(" ",I7,FIND("Country:",I7))+1)))

VB Code:

Range("K2").Formula = "=IF(ISERR(MID(I2,FIND(" ",I2,FIND("Country:",I2))+1,IF(
               ISERR(FIND("Country:",I2,1)),0,FIND("Process:",I2,1)) - IF(ISERR(FIND("Country:",I2,1)),0,FIND(" ",I2,FIND("Country:",I2))+1))),"UNDEFINED",MID(I2,FIND(" ",I2,FIND("Country:",I2))+1,
               IF(ISERR(FIND("Country:",I2,1)),0,FIND("Process:",I2,1)) - IF(ISERR(FIND("Country:",I2,1)),0,FIND(" ",I2,FIND("Country:",I2))+1)))"
Range("K2").Copy
Range("K2:C10").Pastespecial(XlPasteall)

Upvotes: 0

Views: 357

Answers (1)

Rory
Rory

Reputation: 34045

You need to double all quotation marks in the formula to escape them as they are part of a quoted string in VBA:

Range("K2").Formula = "=IF(ISERR(MID(I2,FIND("" "",I2,FIND(""Country:"",I2))+1,IF(
               ISERR(FIND(""Country:"",I2,1)),0,FIND(""Process:"",I2,1)) - IF(ISERR(FIND(""Country:"",I2,1)),0,FIND("" "",I2,FIND(""Country:"",I2))+1))),""UNDEFINED"",MID(I2,FIND("" "",I2,FIND(""Country:"",I2))+1,
               IF(ISERR(FIND(""Country:"",I2,1)),0,FIND(""Process:"",I2,1)) - IF(ISERR(FIND(""Country:"",I2,1)),0,FIND("" "",I2,FIND(""Country:"",I2))+1)))"

Upvotes: 3

Related Questions