Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF INDEX MATCH formula inside VBA with variables
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: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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDEX - MATCH with three variables | Excel Worksheet Functions | |||
variables inside a formula?? | Excel Programming | |||
Index Match function for multiple linked variables | Excel Worksheet Functions | |||
Index Match function for multiple linked variables | Excel Worksheet Functions | |||
Index Match With 3 Variables | Excel Worksheet Functions |