VBA filling column with a formula value

VBA learner ITG

Board Regular
Joined
Apr 18, 2017
Messages
205
Office Version
  1. 365
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?


Code:
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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,604
Office Version
  1. 365
Platform
  1. Windows
Firstly rename your macro, you should never use VBA keywords for the names of variables or procedures.
Once you have done that, try
VBA Code:
        .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

Board Regular
Joined
Apr 18, 2017
Messages
205
Office Version
  1. 365
Firstly rename your macro, you should never use VBA keywords for the names of variables or procedures.
Once you have done that, try
VBA Code:
        .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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,604
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

VBA learner ITG

Board Regular
Joined
Apr 18, 2017
Messages
205
Office Version
  1. 365
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?

VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,604
Office Version
  1. 365
Platform
  1. Windows
You could add this to the end of your code, to convert them to values
VBA Code:
With Range("F4:I" & LastRow)
   .Value = .Value
End With
 

Watch MrExcel Video

Forum statistics

Threads
1,127,699
Messages
5,626,382
Members
416,176
Latest member
Dyl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top