VBA filling column with a formula value

[VBA learner ITG]

Hi all,

I am trying to fill down Column C with the formula values until the end of column A and I am getting an error on the formula which I know is correct as if its written as a formula works as expected.

Any advice on what i am doing wrong?

Sub FORMULA()
    Dim LastRow As Long
    With ThisWorkbook.sheets("FUSION_INTERMEDIATE")
        'Assume we want to create values for every cell in column C down until
        'we get to the last cell in column A
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        'Paste the formula
        .Range("C4:C" & LastRow).Formula = "=IFERROR(INDEX(UNIQUE(FILTER(Brief!$B$7:$B$5000,Brief!$AA$7:$AA$5000=A4)),COUNTIFS(A$4:A4,A4)),"")"
        'Convert to values
        .Range("C4:C" & LastRow).Value = _
                   .Range("C4:C" & LastRow).Value
    End With
End Sub

 

[Fluff]

Firstly rename your macro, you should never use VBA keywords for the names of variables or procedures.
Once you have done that, try

        .Range("C4:C" & LastRow).Formula2 = "=IFERROR(INDEX(UNIQUE(FILTER(Brief!$B$7:$B$5000,Brief!$AA$7:$AA$5000=A4)),COUNTIFS(A$4:A4,A4)),"""")"

 

[VBA learner ITG]

Firstly rename your macro, you should never use VBA keywords for the names of variables or procedures.
Once you have done that, try

        .Range("C4:C" & LastRow).Formula2 = "=IFERROR(INDEX(UNIQUE(FILTER(Brief!$B$7:$B$5000,Brief!$AA$7:$AA$5000=A4)),COUNTIFS(A$4:A4,A4)),"""")"

Thnak you Fluff for your assistance again, I cannot thank you enough for your time.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[VBA learner ITG]

Can I get your guidance as you are probably aware I am learning VBA and using google and this site for reference points on how to accomplish tasks.

My question is:

If I wanted to apply the above logic but to numerous columns by using VBA is there a recommend approach for this or can you recommend a site/forum for me to read up on?

For example:

Column B through to column CV to end row 11983 currently has various unique formulas in it.

I want to accomplish something like the above but in this format but in the cell instead of the formula the formula values?

Range("F4:F" & LastRow).Formula = "=IFERROR(VLOOKUP($C4,Brief!$B:$AD,16,FALSE),"""")"
Range("G4:G" & LastRow).Formula = "=IFERROR(VLOOKUP($C4,Brief!$B:$AD,17,FALSE),"""")"
Range("H4:H" & LastRow).Formula = "=IFERROR(VLOOKUP($C4,Brief!$B:$AD,18,FALSE),"""")"
Range("I4:I" & LastRow).Formula = "=IFERROR(VLOOKUP($C4,Brief!$B:$AD,19,FALSE),"""")"

 

[Fluff]

You could add this to the end of your code, to convert them to values

With Range("F4:I" & LastRow)
   .Value = .Value
End With