Vlookup - finding multiple occurances

Hi all!

I have been trying to find all occurances of a value in a 14,000+ row table. I finally found this pice of code:

'=IF(COUNTIF(INDEX(tbl,0,1),G$1)>ROW()-ROW(H$1),OFFSET(tbl,SMALL(IF(INDEX(tbl,0,1)=G$1,ROW(tbl)-CELL("Row",tbl)),ROW()-ROW(H$1)+1),1,1,1),"")

It works excellently well, but needs to be entered directly into a cell as an array (CTRL + SHIFT + ENTER). When I try to assign this value to a cell using VBA, the "Row" seems to be causing an issue since it uses the dbl quotes.

Can anyone shed a little light on how I might be able to get VBA to accept this?

Many thanks in advance!!!

One of several methods is to simply double up your quotes...

Range("A1").FormulaArray = "=IF(COUNTIF(INDEX(tbl,0,1),G$1)>ROW()-ROW(H$1),OFFSET(tbl,SMALL(IF(INDEX(tbl,0,1)=G$1,ROW(tbl)-CELL(""Row"",tbl)),ROW()-ROW(H$1)+1),1,1,1),"""")"

Tom,

Thanks so much. So simple,yet i could not figure it out.
A follow up question, if I might:
Now that they array is working perfectly during the first loop in the code, the cell references to A$1 and C$1 need to change as the code runs thru the 14,000+ lines. The Columns A and C remain contant, but the 'row number'(shown as 1) need to change based upon the variables "linestoinsert" and "rng" respectively. I think I am having more double, triple quote and ampersand issues...

 ActiveCell.FormulaArray = "=IF(COUNTIF(INDEX(tbl2,0,1),A$1)>ROW()-ROW(C$1),OFFSET(tbl2,SMALL(IF(INDEX(tbl2,0,1)=A$1,ROW(tbl2)-CELL(""Row"",tbl2)),ROW()-ROW(c$1)+1),2,1,1),"""")"

BTW, the row needs to be referenced with the $ since the array is pasted to some additional rows before the 'row number' needs to be updated.

Thanks again in advance for any help you could offer!

I'm not clear about what you need?

ActiveCell.FormulaArray = "=IF(COUNTIF(INDEX(tbl2,0,1),A$" & linestoinsert &")>ROW...

Is this your issue here?