IF INDEX MATCH formula inside VBA with variables

[hansjhamm]

I have alot of help with parts of this, but onto another brick wall...

I need for the index section of the formula to go to the next column
and repeat the process...
1st.....IF(INDEX(MAPActive!C:C & icol &......,
2nd......IF(INDEX(MAPActive!D & icol &......, etc...
Or something like this.
So far I have tried numerous combinations and either get #Name? or
nothing at all...

This is one of those times when you change someting it creates another
set of changes



Windows("MAPReport.xls").Activate
Dim LastRow As Long
Dim lastcol As Long
Dim LastRow1 As Long
Dim Lastcol1 As Long
iWkbk = iWkbk + 1
iCol = iCol + 1
Set wbResults = Workbooks.Open(wsFiles.Range("M" &
iWkbk).Value).Worksheets(1)
Set wbResults = Workbooks.Open(wsFiles.Range("MapActive C:C" &
iCol).Value).Worksheets(1)
With Worksheets("MAP")
lastcol = .Cells(40, Columns.Count).End(xlToLeft)(1, 2).Column
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Lastcol1 = .Cells(2, Columns.Count).End(xlToLeft)(1, 2).Column
LastRow1 = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range(.Cells(40, lastcol), .Cells(LastRow, lastcol))
.Formula = "=IF(INDEX(MAPActive!C:C " & iCol &
",MATCH(MAP!D40,MAPActive!A:A,0))="""",IF(ISERROR(SUMPRODUCT((m" &
iWkbk & ".htm!$A$1:$A$10000=$D40)*(m" & iWkbk &
".htm!$E$1:$E$10000=""Yes"")/(MAP!F$1))),"""",SUMPRODUCT((m" & iWkbk &
".htm!$A$1:$A$10000=$D40)*(m" & iWkbk &
".htm!$E$1:$E$10000=""Yes"")/(MAP!F$1)))"

.Value = .Value


End With
End With


Thanks,

Hans