Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a range variable inside a excel function | Excel Discussion (Misc queries) | |||
Variable with no Value inside Macro Equation?? | Excel Programming | |||
Average For Variable-length Column (w N/A value inside) | Excel Programming | |||
Average For Variable-length Column (w N/A value inside) | Excel Programming | |||
variable inside a formula? | Excel Programming |