Index/Match macro

[randolphoralph]

I am trying to get the macro below to work.


The orginal macro was created by OssieMac and the orignal thread is located here
http://www.eggheadcafe.com/software/aspnet/33926005/macro-to-create-indexmatch.aspx

Just wanted to make sure to give credit to the creator.

Sub TestIndexMatch()

Dim rngDataCell As Range
Dim rngIndexArray As Range
Dim rngIndexRange As Range
Dim rngMatchRange As Range

Dim strDataCell As String
Dim strIndexArray As String
Dim strIndexRange As String

Dim lngMatchRangeCol As Long
Dim lngColToInsert As Long

Dim lngIndexArrayColMin As Long

Set rngDataCell = Application.InputBox _
(Prompt:="Select CELL in destination spreadsheet " & _

Set rngIndexArray = Application.InputBox _
(Prompt:="Highlight ARRAY in source spreadsheet to " & _

Set rngIndexRange = Application.InputBox _
(Prompt:="Highlight COLUMN in source spreadsheet where " & _

Set rngMatchRange = Application.InputBox _
(Prompt:="Highlight COLUMN in source spreadsheet where " & _

strDataCell = rngDataCell.Address(0, 0, , True)
strIndexArray = rngIndexArray.Address(1, 1, , True)
strIndexRange = rngIndexRange.Address(1, 1, , True)

lngIndexArrayColMin = rngIndexArray.Cells(1, 1).Column

lngMatchRangeCol = rngMatchRange.Column

lngColToInsert = lngMatchRangeCol - lngIndexArrayColMin + 1

ActiveCell.Formula = "=INDEX(" & strIndexArray & ",MATCH(" & _
strDataCell & "," & strIndexRange & ",0)," & _
lngColToInsert & ")"

End Sub

The issue is that when I paste this into Excel 2010 VBA it highlights the following in red font and gives me the message "Expected Expression"

Set rngDataCell = Application.InputBox _
(Prompt:="Select CELL in destination spreadsheet " & _
 
Set rngIndexArray = Application.InputBox _
(Prompt:="Highlight ARRAY in source spreadsheet to " & _
 
Set rngIndexRange = Application.InputBox _
(Prompt:="Highlight COLUMN in source spreadsheet where " & _
 
Set rngMatchRange = Application.InputBox _
(Prompt:="Highlight COLUMN in source spreadsheet where " & _

Can anyone tell me what the issue is?

 

[VoG]

Each of those Set statements should be like this

Set rngDataCell = Application.InputBox(Prompt:="Select CELL in destination spreadsheet ", Type:=8)

 

[randolphoralph]

That is exactly what I was needing. Thanks Peter/HTH!

 

[randolphoralph]

Here is the macro that I have so far....

Sub IndexMatch()
Dim rngDataCell As Range
Dim rngIndexArray As Range
Dim rngIndexRange As Range
Dim strDataCell As String
Dim strIndexArray As String
Dim strIndexRange As String
Set rngIndexArray = Application.InputBox(Prompt:="Select Range of Values to Return", Type:=8)
Set rngDataCell = Application.InputBox(Prompt:="Select Cell to Match", Type:=8)
Set rngIndexRange = Application.InputBox(Prompt:="Select Range to Match", Type:=8)
strDataCell = rngDataCell.Address(0, 0, , True)
strIndexArray = rngIndexArray.Address(1, 1, , True)
strIndexRange = rngIndexRange.Address(1, 1, , True)

ActiveCell.Formula = "=INDEX(" & strIndexArray & ",MATCH(" & _
strDataCell & "," & strIndexRange & ",0))"
End Sub

I tried to use this macro on 2 seperate workbooks and it will not let me select a range from another workbook.

How would I change it to let me select ranges in the same workbook and seperate workbooks?

 

[randolphoralph]

I know that I can specify the name of the workbook....but I want to be able to use the macro regardless of the workbook I am in....or the workbook that the ranges are in.

 

[randolphoralph]

Well after doing tons of research I do not think this can be accomplished.

If anyone wants a challenge there maybe a way.

 

[randolphoralph]

After thinking about my problem for awhile I realized that Excel will let you insert a function and select ranges from other workbooks so it must be possible.

Does anyone have any ideas on this?