Trouble Inserting Formulas into Cells. Please help.

[wgilthorpe]

I am writing a tool to create quotations at work. Not really in the scope of my job, but it needs to get done. I am having trouble with the below loop. I cannot for the life of me (after much trial and error and the failure of my Google Fu) figure out how to get the below to work. I keep getting runtime error 1004. However, in one of my trials I found that if the first equal sign inside quotes is left out, then the text is inserted into the cells correctly. It is very frustrating. All help will be appreciated.

For i = 13 To 346

If spirSheet.Cells(i, 2).Interior.Color <> 12566463 Then spirSheet.Cells(i, 2).Formula = "=iferror(" & "vlookup(e" & i & ";" & "'" & sheetname & "'" & "!$C$15:$E$1000;3;FALSE)" & "/" & "vlookup(e" & i & ";" & "'" & sheetname & "'" & "!$C$15:$E$1000;2;FALSE));0)"

Next i

The below is the formula that I am trying to insert (included for clarity). Below sheetname = "01-FT-014xxx"

=IFERROR(VLOOKUP(E13;'01-FT-014xxx'!$C$15:$E$1000;3;FALSE)/(VLOOKUP(E13;'01-FT-014xxx'!$C$15:$E$1000;2;FALSE));0)

 

[Peter_SSs]

Welcome to the MrExcel board!

Instead of looping through the rows one at a time, you should be able to insert all the formulas at once - see below.
When inserting formulas through vba use a comma delimiter, not the semicolon. They should get converted to semicolons automatically as the formulas are entered in the worksheet. (vba doesn't have the regional differences that worksheet functions have)
Instead of continually breaking the string to insert variables, this trick of using Replace makes the construction easier to follow (for me at least )

So, instead of that whole loop you posted, try this single line.

spirSheet.Range("B13:B346").Formula = Replace("=IFERROR(VLOOKUP(E13,'#'!$C$15:$E$1000,3,FALSE)/(VLOOKUP(E13,'#'!$C$15:$E$1000,2,FALSE)),0)", "#", sheetname)

 

[wgilthorpe]

Thank you. This works very well except that I have other formulas in the cells that are colored and this replaces them. Is there any way to make the loop work?

 

[Peter_SSs]

Thank you. This works very well except that I have other formulas in the cells that are colored and this replaces them. Is there any way to make the loop work?

In my enthusiasm, I forgot about that, sorry.

Yes there is. It is basically what I have in my previous code but applied line by line and shown in the third code below.

However, I'm still wondering (for my own interest really) if there is a possibility of applying all the required formula at once.

1. IF all the non-grey cells happened to be empty then this should do them all at once.

On Error Resume Next
spirSheet.Range("B13:B346").SpecialCells(xlBlanks).FormulaR1C1 = _
  Replace("=IFERROR(VLOOKUP(RC[3],'#'!R15C3:R1000C5,3,FALSE)/(VLOOKUP(RC[3],'#'!R15C3:R1000C5,2,FALSE)),0)", "#", sheetname)
On Error GoTo 0

2. IF all the non-grey cells have no other colour then this should do them all at once.

With spirSheet.Range("B12:B346")
  .AutoFilter Field:=1, Operator:=xlFilterNoFill
  On Error Resume Next
  .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).FormulaR1C1 = _
    Replace("=IFERROR(VLOOKUP(RC[3],'#'!R15C3:R1000C5,3,FALSE)/(VLOOKUP(RC[3],'#'!R15C3:R1000C5,2,FALSE)),0)", "#", sheetname)
  On Error GoTo 0
  .AutoFilter
End With

3. IF neither of the above apply, or you just want to use the loop anyway ..

For i = 13 To 346
  If spirSheet.Cells(i, 2).Interior.Color <> 12566463 Then spirSheet.Cells(i, 2).Formula = _
    Replace(Replace("=IFERROR(VLOOKUP(E@,'#'!$C$15:$E$1000,3,FALSE)/(VLOOKUP(E@,'#'!$C$15:$E$1000,2,FALSE)),0)", "#", sheetname), "@", i)
Next i

 

[wgilthorpe]

I apologize for the delay. Thank you so much. You have been a life saver. This works very well.

 

[Peter_SSs]

I apologize for the delay. Thank you so much. You have been a life saver. This works very well.

Not sure which one you used but glad one of them was suitable. Thanks for letting us know.