Index Match with Variables Issue

K

Kellogg

New Member
Joined
Mar 30, 2013
Messages
41
Platform
  1. Windows
  2. MacOS
Code: 
Dear Great Minds of Excel,

I have a VBA code that works well with a set range.  When I add variables into the code I get issues.  Can anyone assist?  


DATA
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TABLE="width: 322"]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT]<colgroup><col width="64" style="width: 48pt; mso-width-source: userset; mso-width-alt: 2340;">[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT]<col width="40" style="width: 30pt; mso-width-source: userset; mso-width-alt: 1462;">[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT]<col width="64" style="width: 48pt;">[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT]<col width="50" style="width: 38pt; mso-width-source: userset; mso-width-alt: 1828;">[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT]<col width="64" style="width: 48pt;">[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT]<col width="64" style="width: 48pt;">[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT]<col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;">[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT]<tbody>[TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="width: 64, bgcolor: #339966"][FONT=Calibri][SIZE=2][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="width: 40, bgcolor: #339966"][FONT=Calibri][SIZE=2][COLOR=#339966][/COLOR][/SIZE][/FONT]Col C
[/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="width: 64, bgcolor: #339966"][FONT=Calibri][SIZE=2][COLOR=#ffffff] Col D[/COLOR][/SIZE][/FONT]
[/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="width: 50, bgcolor: #339966"][FONT=Calibri][SIZE=2][COLOR=#ffffff] Col E[/COLOR][/SIZE][/FONT]
[/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="width: 64, bgcolor: #339966"][FONT=Calibri][SIZE=2][COLOR=#ffffff] Col F[/COLOR][/SIZE][/FONT]
[/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="width: 64, bgcolor: #339966"][FONT=Calibri][SIZE=2][COLOR=#ffffff]Col G[/COLOR][/SIZE][/FONT]
[/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="width: 82, bgcolor: #339966"][FONT=Calibri][SIZE=2][COLOR=#ffffff]Col H[/COLOR][/SIZE][/FONT]
[/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: #339966"][FONT=Calibri][SIZE=2][COLOR=#000000] Row[/COLOR][/SIZE][/FONT]
[/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: #339966"][B][FONT=Calibri][SIZE=2][COLOR=#ffffff]Rec. #[/COLOR][/SIZE][/FONT][/B]
[/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: #339966"][B][FONT=Calibri][SIZE=2][COLOR=#ffffff]Date[/COLOR][/SIZE][/FONT][/B][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: #339966"][B][FONT=Calibri][SIZE=2][COLOR=#ffffff]Site[/COLOR][/SIZE][/FONT][/B][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: #339966"][B][FONT=Calibri][SIZE=2][COLOR=#ffffff]Coop ID[/COLOR][/SIZE][/FONT][/B][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: #339966"][B][FONT=Calibri][SIZE=2][COLOR=#ffffff]Coop Type[/COLOR][/SIZE][/FONT][/B]
[/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: #339966"][FONT=Calibri][SIZE=2][COLOR=#ffffff][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=2][COLOR=#ffffff]Date Harvested[/COLOR][/SIZE][/FONT]
[/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"]4
[/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Calibri][SIZE=2][COLOR=#000000]1[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Calibri][SIZE=2][COLOR=#000000]11/26/14[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Calibri][SIZE=2][COLOR=#000000]2[/COLOR][/SIZE][/FONT]
[/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Calibri][SIZE=2][COLOR=#000000]Lich114[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Calibri][SIZE=2][COLOR=#000000]Private[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Calibri][SIZE=2][COLOR=#000000]10/25/14[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT]
[/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"]5
[/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Calibri][SIZE=2][COLOR=#000000]2[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Calibri][SIZE=2][COLOR=#000000]11/26/14[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Calibri][SIZE=2][COLOR=#000000]1[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Calibri][SIZE=2][COLOR=#000000]Krys114[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Calibri][SIZE=2][COLOR=#000000]Private[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Calibri][SIZE=2][COLOR=#000000]10/25/14[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT]
[/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"]6
[/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT]
[/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"]7
[/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT]
[/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT]</tbody>[/TABLE]
On a userform I have two Drop Down Menus to select a Coop ID from column F and a Harvest date from column H.  I have a find button to pull the Record Number in column C.

The following works:

cbxHarvest2CoopID 'selected from column F
cbxReturnDateHarvested 'selected from column H


[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT]ActiveWorkbook.Names.Add Name:="X", RefersTo:=cbxHarvest2CoopID
    ActiveWorkbook.Names.Add Name:="Y", RefersTo:=cbxReturnDateHarvested
    myValue = Evaluate("INDEX(C4:C6,MATCH(X & Y,F4:F6 & H4:H6,0))")
    MsgBox "mvValue = " & myValue
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT]

if cbxHarvest2CoopID = Lich114 and cbxReturnDateHarvested is 10/25/14

The value returned record # from column C is 2


[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT]When I try to add variables to the VBA code I get an error.  Here is what I have a problem with:

'
'Set up variables for ranges
    Dim SrchAnsr As Range 'Record # Column C
    Dim SrchX As Range 'Coop ID Column F
    Dim SrchY As Range 'Harvest Date Column H


'Set up ranges
    Srchlast = (Worksheets("Return_Data").Range("C" & Rows.Count).End(xlUp).Row) + 1
    Set SrchAnsr = Worksheets("Return_Data").Range("C4:C" & Srchlast)
    Set SrchX = Worksheets("Return_Data").Range("F4:F" & Srchlast)
    Set SrchY = Worksheets("Return_Data").Range("H4:H" & Srchlast) 'Date entered




I replace C4:C6 with SrchAnsr, F4:F6 with SrchX, and H4:H6 with SrchY and I get a mismatch error.  Where did I go wrong?

ActiveWorkbook.Names.Add Name:="X", RefersTo:=cbxHarvest2CoopID
    ActiveWorkbook.Names.Add Name:="Y", RefersTo:=cbxReturnDateHarvested
    myValue = Evaluate("INDEX(SrchAnsr,MATCH(X & Y,SrchX & SrchY,0))")
    MsgBox "mvValue = " & myValue
'

As Always, Thanks...

Craig
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Does this work for you?

Code: 
myValue = Evaluate("INDEX(SrchAnsr,MATCH(X & Y," & SrchX.Address & " & " & SrchY.Address & ",0))")

You are evaluating a string so you can't use Range objects in it directly.
 
Upvote 0
Andrew.

The code still errors. I played around with addresses and found the error is the variable for the search column. When I replace SrchAnsr with C4:6 the code works. Any thoughts why? The SrchX.address and SrchY.address works fine. Thank for your help.
 
Upvote 0
Sorry, I missed that bit, but the same principle applies:

Code: 
myValue = Evaluate("INDEX(" & SrchAnsr.Address & ",MATCH(X & Y," & SrchX.Address & " & " & SrchY.Address & ",0))")
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Help Index Match
Replies
3
Views
350
KRice
K
M
  • Solved
Help with separating Text String from a Excel Col
Replies
3
Views
428
Peter_SSs
Peter_SSs
L
  • Solved
INDEX MATCH MAXIFS / AGGREGATE - Multiple criteria
Replies
3
Views
326
Fluff
Fluff
J
  • Question
Index Match Formula to generate a report duplicates? Rosters/Vacancies
Replies
9
Views
605
Jay88
J
Red over White
  • Solved
Index match where one of the elements is ideally a variable
Replies
4
Views
280
jtakw
jtakw

Forum statistics

Threads
1,216,146
Messages
6,129,142
Members
449,488
Latest member
qh017

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top