Variable inside a formula
 

All,

Tom Ogilvy has helped me on part of this, but one thing always leads to
another...
The sumproduct section works perfect, thanks Tom!

But, I took his idea and applied it to the IF(Index(match) section and
I cannot get it to work. It returns the #Name?

The IF(index(match) section needs to move incrementally across the
columns of the CMAPActive sheet...The formula works but cannot get it
to move for this section.

Below is what I have at this point...

I have called num at the beginning of the code As Variant

Windows("CMAP.xls").Activate
Dim LastRow As Long
Dim lastcol As Long
iWkbk = iWkbk + 1
num = 1
num = num + 1

Set wbResults = Workbooks.Open(wsFiles.Range("C" &
iWkbk).Value).Worksheets(1)
With Worksheets("CMAP")
lastcol = .Cells(40, Columns.Count).End(xlToLeft)(1, 2).Column
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range(.Cells(40, lastcol), .Cells(LastRow, lastcol))
.Formula = "=IF(INDEX(CMAPActive! C:C" & num &
",MATCH($C40,CMAPActive!$A:$A,0))="""",IF(ISERROR( SUMPRODUCT((c" &
iWkbk & ".htm!$A$1:$A$10000=$C40)*(c" & iWkbk &
".htm!$E$1:$E$10000=""Yes"")/(CMAP!F$39))),"""",SUMPRODUCT((c" & iWkbk
& ".htm!$A$1:$A$10000=$C40)*(c" & iWkbk &
".htm!$E$1:$E$10000=""Yes"")/(CMAP!F$39))),""Inactive"")"
.Value = .Value


End With
End With



Thanks
Hans