Insert a Formula in a range which includes variant

Question

I would like to write a code to insert below formula in the range("c6:c205"). =IFERROR(VLOOKUP(C6;Pricelist!A5:F21997;2;FALSE);" ")

Could you please help me to correct the following code. Thank you in advance.


For i = 6 To 205
Dim myFormula As String        
        myFormula = "=IFERROR(VLOOKUP(C"
        myFormula = myFormula & i
        myFormula = myFormula & ";Pricelist!A5:F21997;2;FALSE);"
        myFormula = myFormula & " "" "
        myFormula = myFormula & ")"
        
        
        Range("E" & i).Formula = myFormula
        
 Next

What exactly is the problem? – Luuklag Jun 22 '20 at 12:18 — Luuklag, Jun 22 '20 at 12:18

SJR · Accepted Answer · 2020-06-22T12:57:10.607

0

You can just do it in one without a loop, it will automatically adjust.

range("e6:e205").formula="=IFERROR(VLOOKUP(C6,Pricelist!$A$5:$F$21997,2,FALSE),"" "")

In VBA you don't use local settings such as ;

And you need to double-up the quotes.

edited Jun 22 '20 at 12:57

answered Jun 22 '20 at 12:17

SJR

22,986
6
18
26

1

Thank you so much for your help – AM-NL Jun 22 '20 at 12:56
could you please guide me how I can put the following formula in the cell as well? =SUMIFS(G6:GG;G6:GG;"<>#VALUE!"; G6:GG; "<>#N/A") currently I am using the following and it works, but not exactly what I want ActiveSheet.Range("G" & LastrowAS).Formula = "=SUM(G3 :GG)" when I replace sumif with sum formula in the code, i receive an error – AM-NL Jun 25 '20 at 10:57
Could you start a new question please. G3:GG is not a valid reference as you need a row number too. – SJR Jun 25 '20 at 11:12

score 0 · Answer 2 · answered Jun 22 '20 at 12:24

0

You need not have so many lines of code to get this. Try:

Sub AddFormula()

Dim myFormula As String
myFormula = "=IFERROR(VLOOKUP(C6,pricelist!A5:F21997,2,FALSE),"""")"

For i = 6 To 10
    Range("E" & i).Formula = myFormula
Next i
End Sub

answered Jun 22 '20 at 12:24

PerlBatch

200
2
2
10

score 0 · Answer 3 · answered Jun 22 '20 at 12:26

Use commas and fix some double-quotes:

Sub ksdjfhs()
    dq = Chr(34)
    For i = 6 To 205
        Dim myFormula As String
        myFormula = "=IFERROR(VLOOKUP(C"
        myFormula = myFormula & i
        myFormula = myFormula & ",Pricelist!A5:F21997,2,FALSE),"
        myFormula = myFormula & dq & dq
        myFormula = myFormula & ")"
            
        Range("E" & i).Formula = myFormula
 Next
End Sub

Insert a Formula in a range which includes variant

3 Answers3