none
how to solve mismatch error in INDEX & MATCH function in VBA Microsoft Excel 2010 RRS feed

  • Question

  • Hello

    I wrote a code in vba to find a cell with two criteria. I uploaded a sample of my file into this link:

    http://s000.tinyupload.com/?file_id=00243748825638974221

      

    here is the code :

    Sub regionalAverage()
    
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    ActiveSheet.DisplayPageBreaks = False
    
    ' *** change the declaration here ***
    '
    Dim address() As String
    Dim rw As Variant
    Dim col As Variant
    Dim date_ini As Date
    Dim date_fin As Date
    
    'create WorkSheet
    '
    ActiveWorkbook.Sheets.Add(After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)).Name = "Output"
    Range("A1").Value = "majid"
    
    ' *** add Redim here, so the index of the array will start
     from 1 ***
    '
    ReDim address(1 To 2)
    
    date_ini = #1/1/2008#
    date_fin = #1/2/2008#
    For j = 1 To 3
        For conteo = date_ini To date_fin
            For i = 1 To 2
                With Sheets(i)
                   With Application
                   
                        col = .Match(j, Worksheets(i).Range("F2:F23393"), 0)
                        rw = .Match(CLng(conteo), Worksheets(i).Range("D2:D23393"), 0)
    
                        address(i) = .Index(Worksheets(i).Range("H2:H23393"), rw, col)  ' switched between rw (i think it's your row reference) and col
    
             
    
                   End With
    
            
                End With
            Next i
    
        '    computation
    '
            area = 6.429571
            Sheets("Output").Activate
            Range("A1").Select
            ActiveCell.Offset(0, j).Select
            colname = Split(ActiveCell(1).address(1, 0), "$")(0)
            Columns("" & colname & ":" & colname & "").Select
            Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, _
                 LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                 MatchCase:=False, SearchFormat:=False).Select
    
            ActiveCell.Value = "=(SUM(" & address(1) & "," & address(2) & "))/" & area & ""
    
        Next conteo
    Next j
    
    End Sub
    
    Thank you all.


    Tuesday, July 12, 2016 1:07 PM

Answers

  • Sub TestWorks()
        Dim conteo As Date
        Dim rw As Variant
        Dim j As Integer
        Dim i As Integer
        Dim aname(1 To 1) As Variant
        
        conteo = #1/1/2008#
        j = 1
        i = 1
        
        Worksheets(1).Range("AA2:AA23393").Formula = "=TEXT(D2,""m/d/yyyy"")&F2"
        rw = Application.Match(conteo & j, Worksheets(1).Range("AA2:AA23393").Value, 0)
        If IsError(rw) Then
            MsgBox "Not found."
        Else
            aname(1) = Worksheets(i).Range("H2:H23393")(rw).Value
            MsgBox aname(1)
        End If
        Worksheets(1).Range("AA2:AA23393").Clear
        
    End Sub
    Wednesday, July 13, 2016 1:15 PM
  • Majid,

     You may want to try adding these two statements after the dim statements

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False

     Then before 'end sub' add these two statements

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

    Turning these off an on have sped up a lot of my sub routines.

    HTH

    Harry

     

    Friday, July 15, 2016 5:02 PM

All replies

  • You are using a row as a column index, and that just won't work.  Clearly, you are trying to match criteria in two columns to find a cell in a third.  But it would be better to fully describe what you want the code to do, rather than show code that doesn't do what you want.  You should probably be able to just write a SUMIFS formula into the active cell(s) to get what you want. 

    A lot of your code can be simplified. For example

            Sheets("Output").Activate
            Range("A1").Select
            ActiveCell.Offset(0, j).Select
            colname = Split(ActiveCell(1).Address(1, 0), "$")(0)
            Columns("" & colname & ":" & colname & "").Select
            Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, _
                 LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                 MatchCase:=False, SearchFormat:=False).Select

             ActiveCell.Value = "=(SUM(" & Address(1) & "," & Address(2) & "))/" & area & ""

    Could just be

            With Sheets("Output")
            .Cells(.Rows.Count,j).End(xlUp)(2).Formula = "=(SUM(" & address(1) & "," & address(2) & "))/" & area & ""
           End With

     
    Tuesday, July 12, 2016 2:23 PM
  • Thanks for your attention.

    I uploaded a sample of my file in here : http://s000.tinyupload.com/?file_id=00243748825638974221

    my main problem is just to find a cell in a sheet with two criteria with index and match functions and put the value in an array to use in sum function at the end of code. The first criteria is  date that changes (as you can see : conteo from date_ini to date_fin) the second criteria is just an integer that changes too (in the code is : j ).

    Thank you.

    Tuesday, July 12, 2016 2:46 PM
  • I'm not going to create an account to get to your file.

    Figure out a formula that works, along the lines of this:

    =SUMIFS(Sheet1!M:M,Sheet1!D:D,DATEVALUE("1/1/2008"),Sheet1!F:F,1)

    and then create a family of those formulas to use instead of your loops

    =SUMIFS(Sheet1!M:M,Sheet1!D:D,DATEVALUE("1/1/2008"),Sheet1!F:F,1)
    =SUMIFS(Sheet1!M:M,Sheet1!D:D,DATEVALUE("1/1/2008"),Sheet1!F:F,2)
    =SUMIFS(Sheet1!M:M,Sheet1!D:D,DATEVALUE("1/1/2008"),Sheet1!F:F,3)
    =SUMIFS(Sheet1!M:M,Sheet1!D:D,DATEVALUE("1/2/2008"),Sheet1!F:F,1)
    =SUMIFS(Sheet1!M:M,Sheet1!D:D,DATEVALUE("1/2/2008"),Sheet1!F:F,2)
    =SUMIFS(Sheet1!M:M,Sheet1!D:D,DATEVALUE("1/2/2008"),Sheet1!F:F,3)

    =SUMIFS(Sheet2!M:M,Sheet2!D:D,DATEVALUE("1/1/2008"),Sheet2!F:F,1)
    =SUMIFS(Sheet2!M:M,Sheet2!D:D,DATEVALUE("1/1/2008"),Sheet2!F:F,2)
    =SUMIFS(Sheet2!M:M,Sheet2!D:D,DATEVALUE("1/1/2008"),Sheet2!F:F,3)
    =SUMIFS(Sheet2!M:M,Sheet2!D:D,DATEVALUE("1/2/2008"),Sheet2!F:F,1)
    =SUMIFS(Sheet2!M:M,Sheet2!D:D,DATEVALUE("1/2/2008"),Sheet2!F:F,2)
    =SUMIFS(Sheet2!M:M,Sheet2!D:D,DATEVALUE("1/2/2008"),Sheet2!F:F,3)
    Tuesday, July 12, 2016 3:27 PM
  • Thanks Mr.Deitrick . my file is too large, it has six columns and 23393 rows in each sheet. should I write for all of them !!?
    Tuesday, July 12, 2016 3:44 PM
  • I still have no firm idea what you want to do - perhaps you should look at using a pivot table, which will summarize data based on whatever criteria you need.
    Tuesday, July 12, 2016 5:26 PM
  • how can I write below formula  in macro and write its output to a string array :=INDEX(H2:H23393,MATCH(DATEVALUE("1/1/2008")&F2,D2:D23393&F2:F23393,0))

    Thanks

    Tuesday, July 12, 2016 6:26 PM
  • Something like:

        With Range("AA2:AA23393")
            .Formula = "=INDEX($H$2:$H$23393,MATCH(DATEVALUE(""1/1/2008"")&F2,$D$2:$D$23393&$F$2:$F$23393,0))"
            Application.CalculateFull
            .Value = .Value
        End With

    Tuesday, July 12, 2016 6:59 PM
  • Thank you , But I want put the output of formula into arrays sth like below :

    sub regionalAverage()

    dim aname(i) as string ReDim Formula(1 To 2) date_ini = #1/1/2008# date_fin = #1/2/2008# For j = 1 To 3 For conteo = date_ini To date_fin For i = 1 To 2 With Sheets(i) With Application col = .Match(j, Worksheets(i).Range("F2:F23393"), 0) rw = .Match(CLng(conteo), Worksheets(i).Range("D2:D23393"), 0) aname(i) = CStr(Worksheets(i).Range("H2:H23393").Cells(rw, col).Value) MsgBox aname(i) End With End With Next i next coneto next j

    end sub




    Tuesday, July 12, 2016 7:30 PM
  • That code tries to put 12 values (3 values of j)*(2 values of dates)*(2 sheets) into 2 memory slots  (aname(1)and aname(2)).  For me to help you, you need to explain what you are doing or put a file somewhere that I can get to it without creating an account - like onedrive.live.com    And for the example file that you use, explain the 12 values that you want from those 12 cases.
    Tuesday, July 12, 2016 8:49 PM
  • suppose I want to convert this formula to vba macro format while the date ("1/1/2008") and F2 changing in loop 

    :"=INDEX($H$2:$H$23393,MATCH(DATEVALUE(""1/1/2008"")&F2,$D$2:$D$23393&$F$2:$F$23393,0))"

    I wrote the code above like below but I encounter error :


    sub m()

    conteo = 01/01/2008

    j =1

    With Application rw = .Match(DateValue("conteo") & j, Worksheets(1).Range("D2:D23393") & Worksheets(1).Range("F2:F23393"), 0) aname(1) = .Index(Worksheets(i).Range("H2:H23393"), rw) end with

    end sub


    Do you know whats the problem ?




    Tuesday, July 12, 2016 8:53 PM
  • If I had your email I will share the file on dropbox.com.
    Tuesday, July 12, 2016 8:58 PM
  • this link is where my file uploaded and does not need to sign in :

    http://s000.tinyupload.com/?file_id=00243748825638974221


    Tuesday, July 12, 2016 8:59 PM
  • Hi Majid Javanmard,

    The website on which you had shared your file is blocked at our side. so we are not able to access the file uploaded by you.

    if it is possible try to use different website and again upload the file on that site and provide link to us.

    if we talk about your formula and if it not work as per your expectation then first try to break it in several parts.

    try to execute each separately and make sure it work correctly.

    once they all work correctly then try to merge them one by one and make it whole formula like this.

    if we can access your file we can also try to make a test with that.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, July 13, 2016 3:17 AM
    Moderator
  • Sub TestWorks()
        Dim conteo As Date
        Dim rw As Variant
        Dim j As Integer
        Dim i As Integer
        Dim aname(1 To 1) As Variant
        
        conteo = #1/1/2008#
        j = 1
        i = 1
        
        Worksheets(1).Range("AA2:AA23393").Formula = "=TEXT(D2,""m/d/yyyy"")&F2"
        rw = Application.Match(conteo & j, Worksheets(1).Range("AA2:AA23393").Value, 0)
        If IsError(rw) Then
            MsgBox "Not found."
        Else
            aname(1) = Worksheets(i).Range("H2:H23393")(rw).Value
            MsgBox aname(1)
        End If
        Worksheets(1).Range("AA2:AA23393").Clear
        
    End Sub
    Wednesday, July 13, 2016 1:15 PM
  • Thank you Mr Deitrick , It works and I want make it as answer but it is too slow,  I think the main reason of running slow is filling cells, here I mean:   Worksheets(1).Range("AA2:AA23393").Formula = "=TEXT(D2,""m/d/yyyy"")&F2"

    Is not any way the code using only Index and Match functions ?!

    What is you opinion?!

    Thursday, July 14, 2016 8:41 AM
  • Hi Majid Javanmard,

    The reason to run slow is that your range is also a big.

    so it takes time to fill the cells.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, July 15, 2016 8:06 AM
    Moderator
  • Majid,

     You may want to try adding these two statements after the dim statements

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False

     Then before 'end sub' add these two statements

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

    Turning these off an on have sped up a lot of my sub routines.

    HTH

    Harry

     

    Friday, July 15, 2016 5:02 PM