add a formula to cell using loop

[gtd526]

Hello,
Looking to add a formula to a selected cell containing an Error.
I can't get the formula into the cell.
thanks

Dim c As Range

    Sheets("Main2").Select

    For Each c In Range("C3:C200")

        If c.Value = CVErr(xlErrNA) Then
          'enter formula
          c.Formula = "=INDEX('[The Whole Enchilada.xlsm]PotInvests'!$E$9:$E$30,MATCH(A:A,'[The Whole Enchilada.xlsm]PotInvests'!$A$9:$A$30,0))"
        End If

    Next c

 

[Alex Blakenburg]

What are you actually trying to do ?
Match(A:A is trying use every value in Column A as a lookup value. The concept is problematic not to mention it needs Excel to be dynamic array capable which is not the case for Excel 2019.
In MS 365 it will return a full $1M values and as such would only work if you place the formula in row 1.

Note: befor trying to trouble shoot a formula in VBA make sure you have a working version directly in Excel first.

 

[gtd526]

What are you actually trying to do ?
Match(A:A is trying use every value in Column A as a lookup value. The concept is problematic not to mention it needs Excel to be dynamic array capable which is not the case for Excel 2019.
In MS 365 it will return a full $1M values and as such would only work if you place the formula in row 1.

Note: befor trying to trouble shoot a formula in VBA make sure you have a working version directly in Excel first.

I wanted to fill the cell (C3:C200) with results from other worksheets, using the Index,Match formula.
By showing the code, I wondered if there was a common error someone could see in the code.
I found a solution to insert a formula(s) into the entire range. If some cells were #N/A, I would enter another formula into the #N/A cells. I would repeat this 6 (worksheets) times until all were filled correctly. A:A would reference Col A regardless of the row number.

   LastRow = Cells(Rows.Count, "A").End(xlUp).Row
   
    Set myrange = Range("C3:C" & LastRow)
    myrange.Select
   
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'add formula to entire range, first
    myrange.Formula = "=INDEX(PotInvests!$E$9:$E$30,MATCH(Main2!A:A,PotInvests!$A$9:$A$30,0))"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
   
'now select all #N/A cells to add new formula

    myrange.SpecialCells(xlCellTypeFormulas, xlErrors).Select
        Selection.Formula = "=INDEX('2x3x'!$E$9:$E$30,MATCH(Main2!A:A,'2x3x'!$A$9:$A$30,0))"
   
    myrange.SpecialCells(xlCellTypeFormulas, xlErrors).Select
        Selection.Formula = "=INDEX(Retirement!$E$9:$E$30,MATCH(Main2!A:A,Retirement!$A$9:$A$30,0))"
   
    myrange.SpecialCells(xlCellTypeFormulas, xlErrors).Select
        Selection.Formula = "=INDEX(Technology!$E$9:$E$30,MATCH(Main2!A:A,Technology!$A$9:$A$30,0))"
   
    myrange.SpecialCells(xlCellTypeFormulas, xlErrors).Select
        Selection.Formula = "=INDEX(Misc!$E$9:$E$30,MATCH(Main2!A:A,Misc!$A$9:$A$30,0))"
   
    myrange.SpecialCells(xlCellTypeFormulas, xlErrors).Select
        Selection.Formula = "=INDEX('Bad Times'!$E$9:$E$30,MATCH(Main2!A:A,'Bad Times'!$A$9:$A$30,0))"
   
    myrange.SpecialCells(xlCellTypeFormulas, xlErrors).Select
        Selection.Formula = "=INDEX(Bonds!$E$9:$E$30,MATCH(Main2!A:A,Bonds!$A$9:$A$30,0))"

 

[Alex Blakenburg]

Not sure this is the best way to go about it but the below should work.
(you really shouldn't have different formulas in different rows in the same column)
Like I said previously you can't use A:A as the lookup value.
Assuming the formula is on the sheet Main2, you don't need to refer to it in the formula.

I have only done 1 of the 6 alternative lookups, I will leave it to you to do the others.
In the formula the "@" is just a place holder for the lookup row in column A and the replace uses the Selection row to swap out the "@".

'add formula to entire range, first
    myrange.Formula = "=INDEX(PotInvests!$E$9:$E$30,MATCH($A2,PotInvests!$A$9:$A$30,0))"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
'now select all #N/A cells to add new formula

    myrange.SpecialCells(xlCellTypeFormulas, xlErrors).Select
        Selection.Formula = Replace("=INDEX('2x3x'!$E$9:$E$30,MATCH($A@,'2x3x'!$A$9:$A$30,0))", "@", Selection.Row)