Results 1 to 4 of 4

Problem with variable types in an index/match function

Threaded View

  1. #1
    Registered User
    Join Date
    05-14-2014
    MS-Off Ver
    Excel 2013
    Posts
    14

    Problem with variable types in an index/match function

    Hi,
    I've been testing a piece of code but can only get it to work when I set my variables to string values and look up an alpha character. When I change my variable to integer and look for a number, I get a "Type Mismatch Error 13". It seems to have something to do with the "evaluate" formula, but not sure. Your suggestions are welcome.

    Below are two versions of my code. FindABC() works, while Find123() gets an error. A test worksheet with these routines are also attached.
    Many thanks!
    Cesar

    Private Sub FindABC()
    'This routine finds cell address C3 using match in column A (alpha)
    Dim FindWhat   As String
    Dim CAddress   As String
    Dim LastRow    As Long
        FindWhat = "C"
        Sheets("sheet1").Activate
            LastRow = Cells(Rows.Count, "A").End(xlUp).Row
                MsgBox "Last Row is " & LastRow
            CAddress = Evaluate("=CELL(""address"",INDEX(C1:C" & LastRow & ",MATCH(""" & FindWhat & """,A1:A" & LastRow & ",0)))")
                MsgBox "Cell address is " & CAddress
    End Sub
    
    
    Private Sub Find123()
    'This routine should finds cell address C3 using match in column B (numeric), but comes up with a Type Mismatch Error 13
    Dim FindWhat   As Integer
    Dim CAddress   As String
    Dim LastRow    As Long
    
        FindWhat = "3"
        Sheets("sheet1").Activate
            LastRow = Cells(Rows.Count, "A").End(xlUp).Row
                MsgBox "Last Row is " & LastRow
            CAddress = Evaluate("=CELL(""address"",INDEX(C1:C" & LastRow & ",MATCH(""" & FindWhat & """,B1:B" & LastRow & ",0)))")
                MsgBox "Cell address is " & CAddress
    End Sub
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Variable Row Array in Index Match Function
    By Lacaycer in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-19-2013, 11:45 AM
  2. Adding a variable to index & match function using VBA
    By prestopr in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-07-2013, 05:48 PM
  3. Replies: 2
    Last Post: 11-05-2011, 03:26 PM
  4. Regarding Index & Match function problem
    By ramki in forum Excel General
    Replies: 4
    Last Post: 08-20-2008, 06:15 PM
  5. Index & Match function problem
    By ramki in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-20-2008, 08:13 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1