Macro to create Index/Match
 

?B?T3NzaWVNYWM=?= wrote in
:

Hi Beau,

What version of xl are you using?

I'm using Excel 2003 mainly but would like it to be as generic as
possible.


Index/Match used to be used back I
think about xl97. Vlookup is mostly used now but perhaps there is a
specific need for Index/Match. I have reservations about how much you
can save by using code.

I am asking to use the Index/Match combination rather than Vlookup
because of the flexibility it gives in not needing the sources to be
sorted or lookup value needing to be in the first column.
Regarding time saved, at present if I want to bring in say 5 columns of
data I need to create the first Index/Match formula. Drag and copy
across 5 columns, edit each one (because of the absolute ranges in the
formula they do not update automatically) then copy down the columns. As
I often have to do this then it would be quite a time saver for me. It
could also be a useful tool for my colleagues who don't have the Excel
knowledge to be able to do it from scratch.


Also, how competent are you with VBA. Do you actually need all the
code or just pointers in the right direction? I haven't actually
tested this but I think that Application.InputBox Method could be used
to select a cell or range to be used to build the formula. Would need
to have several inputs to get all the ranges.


I was hoping someone out there would already have got one that works I
could use but some help in developing one would be appreciated.

I've got as far as being able to select the cells/ranges I need (Se
below) but I'm stuck in building the formula. I think it is because I
have ranges not strings (because I wanted to select rather than have to
type into an input box) but not sure.

Sub TestIndexMatch()

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

Set rngDataCell = Application.InputBox _
(Prompt:="Select CELL in destination spreadsheet containing info to be
matched...", Type:=8)
Set rngIndexRange = Application.InputBox _
(Prompt:="Highlight COLUMN in source spreadsheet where matching data is
held...", Type:=8)
Set rngMatchRange = Application.InputBox _
(Prompt:="Highlight COLUMN in source spreadsheet where data to be copied
is held...", Type:=8)

ActiveCell.Formula = "=INDEX(" & rngMatchRange & ",MATCH(" & rngDataCell
& "," & rngIndexRange & ",0),1)"

Also I am not sure how to copy that formula across to bring in multiple
columns eg rngMatchRange being $B:$D not just a single column.

Any help would be appreciated
Beau Nidle

Macro to create Index/Match
 

OK Beau I have put some work in on this. Have not tested to the nth degree
but it should point you in the right direction. I have done this as an
exercise because I cant resist a challenge but I still think that Dave
Petersons comments are extremely valid.

A couple of points that I am not sure if you are aware of. A space and
underscore at the end of a line is a line break in an otherwise single line
of code.

A line break cannot be inserted in the middle of strings enclosed in double
quotes. However, the double quotes can be closed off and then an ampersand
(&) to concatenate followed by the line break and then on the next line the
double quotes are opened again. I used this method in all the InputBox code
but in the last line of code I arranged the line breaks outside of the double
quoted strings where ampersands already existed. (I like to use line breaks
so that the code can be posted in limited space and the code can be copied
and used as is without editing all the lines that are broken by posting.)

The following lines of code return the workbook name as well as sheet name
and cell address. However, when these are inserted in the formula bar in
excel, excel automatically deletes the workbook and sheet name parameters if
not required.

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

The 0,0 returns address as A1 style and 1,1 as $A$1 (absolute). They can
also be mixed to return $A1 or A$1.

Anyway have fun with it and let me know how it goes.

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 " & _
"containing info to be matched...", Type:=8)

Set rngIndexArray = Application.InputBox _
(Prompt:="Highlight ARRAY in source spreadsheet to " & _
"include matching data and data to be copied...", Type:=8)

Set rngIndexRange = Application.InputBox _
(Prompt:="Highlight COLUMN in source spreadsheet where " & _
"matching data is held...", Type:=8)

Set rngMatchRange = Application.InputBox _
(Prompt:="Highlight COLUMN in source spreadsheet where " & _
"data to be copied is held...", Type:=8)

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

--
Regards,

OssieMac

Macro to create Index/Match
 

Sorry for the delay in replying but I've not been well.
Many thanks for the work you've put in here for me. Nearly exactly what I
needed (but then I said it might be too complicated for a generic answer),
but close enough to give me the start I need to work the rest out for
myself.
The bits about returning an absolute/relative reference (0,0 or 1,1) as a
string are very useful to know.
Thanks very much again for your help. If I ever get it working exactly how
I want (not before as there are too many "experts" out there who will
criticise rather than help like you) I'll post it here for you and anyone
else to use. After all one of the great things about this group is that
there is always someone else out there who has tried to do the same thing
as you before. No point in reinventing the wheel as my old manager used to
say.
Cheers
Beau Nidle

Macro to create Index/Match
 

No worries Beau. I look forward to hearing from you again in the future
because I always like to see what improvements can be made to anything I do.


--
Regards,

OssieMac


"Beau Nidle" wrote:

Sorry for the delay in replying but I've not been well.
Many thanks for the work you've put in here for me. Nearly exactly what I
needed (but then I said it might be too complicated for a generic answer),
but close enough to give me the start I need to work the rest out for
myself.
The bits about returning an absolute/relative reference (0,0 or 1,1) as a
string are very useful to know.
Thanks very much again for your help. If I ever get it working exactly how
I want (not before as there are too many "experts" out there who will
criticise rather than help like you) I'll post it here for you and anyone
else to use. After all one of the great things about this group is that
there is always someone else out there who has tried to do the same thing
as you before. No point in reinventing the wheel as my old manager used to
say.
Cheers
Beau Nidle