formulas using vba

[rrocker1405]

Hi,

Since I've a large data to work with when I use the array formula its takes a while to generate information thereby if I wanted use vba to generate results for me. However, my code gets a debugging prompt.

I'm trying to populate the results in column p with the following formula of excel which is using array for indexing.

=IFERROR(INDEX(Table3[ID],SMALL(IF(($N$3=Table3[TabEntType])*(COUNTIF($O$2:O2,Table3[ID])=0),ROW(Table3[TabEntType])-MIN(ROW(Table3[TabEntType]))+1,""),1)),"")

vba:

Sub Test()
  Application.ScreenUpdating = False
  With Worksheets("Sheet1")
  With .Range("p3: P2500")

.FormulaR1C1 = "=IFERROR(INDEX(Table3[ID],SMALL(IF((R3C14=Table3[TabEntType])*(COUNTIF(R2C15:R[-1]C,Table3[ID])=0),ROW(Table3[TabEntType])-MIN(ROW(Table3[TabEntType]))+1,""),1)),"")"
   .Value = .Value

  End With
  End With
  Application.ScreenUpdating = True
End Sub

Thanks in advance.

Kind regards,
A!

 

[NARAYANK991]

Hi Anand ,

In the absence of the file , all I can say is that the "" pair of quotes need to be changed to """" ( 4 double quotes in succession ).

Narayan

 

[rrocker1405]

Hi Narayan,

I'm working on related item(s) countifs to populate information. However, I dont know if there is a easier way to show up information for the same thereby I planned to use index. Attached is the sheet with my criterian stated in i11 cell.

I would be more than happy if there is an easier way to pick information and show up data. I have got more than 20000 entries and this one is a sample for te same.

Thanks in advace.

Kind regards,
Anand

 

[jeffreyweir]

Since I've a large data to work with when I use the array formula its takes a while to generate information thereby if I wanted use vba to generate results for me.

Anand: Using VBA to populate the worksheet with an excel function will not magically make Excel calculate any faster. Excel is still generating the results, not VBA.

Furthermore, it is not clear to me from your sample file exactly what you are wanting to do. Can you elaborate further?

 

[jeffreyweir]

Cross posted at http://forum.chandoo.org/threads/formulas-using-vba.20736/

 

[NARAYANK991]

Hi Anand ,

Looking at your data , I assume it is the same data with reference to which you have asked questions earlier.

I suggest that if you can describe your complete requirement in one go , instead of taking it up in bits and pieces , you are likely to get a proper solution.

It looks to me that your application is a fit candidate for using SQL , which would be extremely fast compared to either using formulae in your worksheet or VBA.

Narayan