INDEX/MATCH formula in VBA to populate text boxes

**Amber_D_Laws** · 02-16-2006, 02:36 PM

In the original version of my quote module I was using the INDEX/MATCH formula below in combination with a drop down box to populate several other cells. I am now working in a multi-page user form, and I would to do a similar action with text boxes.

The formula is as follows:
=INDEX('Catalog Listings'!$B$3:$D$223,MATCH(C30,'Catalog Listings'!$B$3:$B$223,0),1)

So, the question is this. How do I INDEX/MATCH from a comboBox in a userform to several different text boxes (3 to be specific)?

As always any advice is appreciated.
Regards,
Amber

**Amber_D_Laws** · 02-16-2006, 02:49 PM

Above I stated that it was 3 text boxes would need the formula. Actually two will use the INDEX/MATCH, and one will use a simple cell*cell*cell formula. I also need help with that one, but I assume the answer will be much simpler.

Tom Ogilvy · 02-16-2006, 02:55 PM

res = Application.INDEX(Range("Catalog Listings!$B$3:$D$223"), _
Application.MATCH(Combobox1.Value, Range( _
"Catalog Listings!$B$3:$B$223"),0),1)

Textbox1.Value = res

--
Regards,
Tom Ogilvy

"Amber_D_Laws" <[email protected]>
wrote in message
news:[email protected]...
>
> In the original version of my quote module I was using the INDEX/MATCH
> formula below in combination with a drop down box to populate several
> other cells. I am now working in a multi-page user form, and I would to
> do a similar action with text boxes.
>
> The formula is as follows:
> =INDEX('Catalog Listings'!$B$3:$D$223,MATCH(C30,'Catalog
> Listings'!$B$3:$B$223,0),1)
>
> So, the question is this. How do I INDEX/MATCH from a comboBox in a
> userform to several different text boxes (3 to be specific)?
>
> As always any advice is appreciated.
> Regards,
> Amber
>
>
> --
> Amber_D_Laws
> ------------------------------------------------------------------------
> Amber_D_Laws's Profile:
http://www.excelforum.com/member.php...o&userid=30012
> View this thread: http://www.excelforum.com/showthread...hreadid=513312
>

**Amber_D_Laws** · 02-16-2006, 03:07 PM

Great! However, I need to how to define the varible "res"

Originally Posted by Tom Ogilvy

res = Application.INDEX(Range("Catalog Listings!$B$3:$D$223"), _
Application.MATCH(Combobox1.Value, Range( _
"Catalog Listings!$B$3:$B$223"),0),1)

Textbox1.Value = res

--
Regards,
Tom Ogilvy

"Amber_D_Laws" <[email protected]>
wrote in message
news:[email protected]...
>
> In the original version of my quote module I was using the INDEX/MATCH
> formula below in combination with a drop down box to populate several
> other cells. I am now working in a multi-page user form, and I would to
> do a similar action with text boxes.
>
> The formula is as follows:
> =INDEX('Catalog Listings'!$B$3:$D$223,MATCH(C30,'Catalog
> Listings'!$B$3:$B$223,0),1)
>
> So, the question is this. How do I INDEX/MATCH from a comboBox in a
> userform to several different text boxes (3 to be specific)?
>
> As always any advice is appreciated.
> Regards,
> Amber
>
>
> --
> Amber_D_Laws
> ------------------------------------------------------------------------
> Amber_D_Laws's Profile:
http://www.excelforum.com/member.php...o&userid=30012
> View this thread: http://www.excelforum.com/showthread...hreadid=513312
>

**Amber_D_Laws** · 02-16-2006, 03:39 PM

I tried defining as:

Dim res As String
and
Dim res As Variant

both return the following error:

"Run-time error '1004':
Method 'Range' of object '_Global' failed

I'm I doing something wrong, or does the code contain a typo?

Amber

Originally Posted by Amber_D_Laws

Great! However, I need to how to define the varible "res"

Toppers · 02-16-2006, 04:30 PM

Amber,
I tried Tom's code (on Eric's "problem" that you replied to)
and it worked fine so this suggests a typo on your part.

Res will default to variant if you don't DIM it ( and I didn't).

"Amber_D_Laws" wrote:

>
> I tried defining as:
>
> Dim res As String
> and
> Dim res As Variant
>
> both return the following error:
>
> "Run-time error '1004':
> Method 'Range' of object '_Global' failed
>
> I'm I doing something wrong, or does the code contain a typo?
>
> Amber

>
> Amber_D_Laws Wrote:
> > Great! However, I need to how to define the varible "res"
>
>
> --
> Amber_D_Laws
> ------------------------------------------------------------------------
> Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
> View this thread: http://www.excelforum.com/showthread...hreadid=513312
>
>

Tom Ogilvy · 02-16-2006, 04:30 PM

Private Sub ComboBox1_Click()
Dim res As Variant
res = Application.Index(Range("'Catalog Listings'!$B$3:$D$223"), _
Application.Match(ComboBox1.Value, Range( _
"'Catalog Listings'!$B$3:$B$223"), 0), 2)
TextBox1.Value = res
End Sub

worked fine for me. (guess you need the single quotes for the range
argument)

--
Regards,
Tom Ogilvy

"Amber_D_Laws" <[email protected]>
wrote in message
news:[email protected]...
>
> I tried defining as:
>
> Dim res As String
> and
> Dim res As Variant
>
> both return the following error:
>
> "Run-time error '1004':
> Method 'Range' of object '_Global' failed
>
> I'm I doing something wrong, or does the code contain a typo?
>
> Amber

>
> Amber_D_Laws Wrote:
> > Great! However, I need to how to define the varible "res"
>
>
> --
> Amber_D_Laws
> ------------------------------------------------------------------------
> Amber_D_Laws's Profile:
http://www.excelforum.com/member.php...o&userid=30012
> View this thread: http://www.excelforum.com/showthread...hreadid=513312
>

Tom Ogilvy · 02-16-2006, 04:50 PM

It was the spaces in names that was the problem - at least it worked when I
adjusted for that. If I had used

Worksheets("Catalog Listings").Range("B3:D223")

it would have been fine. But apparently when thrown into a range argument
as a single string, it needs the single quotes.

--
Regards,
Tom Ogilvy

"Toppers" <[email protected]> wrote in message
news:[email protected]...
> Amber,
> I tried Tom's code (on Eric's "problem" that you replied
to)
> and it worked fine so this suggests a typo on your part.
>
> Res will default to variant if you don't DIM it ( and I didn't).
>
> "Amber_D_Laws" wrote:
>
> >
> > I tried defining as:
> >
> > Dim res As String
> > and
> > Dim res As Variant
> >
> > both return the following error:
> >
> > "Run-time error '1004':
> > Method 'Range' of object '_Global' failed
> >
> > I'm I doing something wrong, or does the code contain a typo?
> >
> > Amber

> >
> > Amber_D_Laws Wrote:
> > > Great! However, I need to how to define the varible "res"
> >
> >
> > --
> > Amber_D_Laws
> > ------------------------------------------------------------------------
> > Amber_D_Laws's Profile:
http://www.excelforum.com/member.php...o&userid=30012
> > View this thread:
http://www.excelforum.com/showthread...hreadid=513312
> >
> >

Dave Peterson · 02-16-2006, 04:50 PM

dim res as variant

Amber_D_Laws wrote:
>
> Great! However, I need to how to define the varible "res"
>
> Tom Ogilvy Wrote:
> > res = Application.INDEX(Range("Catalog Listings!$B$3:$D$223"), _
> > Application.MATCH(Combobox1.Value, Range( _
> > "Catalog Listings!$B$3:$B$223"),0),1)
> >
> > Textbox1.Value = res
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "Amber_D_Laws"
> > <[email protected]>
> > wrote in message
> > news:[email protected]...
> > >
> > > In the original version of my quote module I was using the
> > INDEX/MATCH
> > > formula below in combination with a drop down box to populate
> > several
> > > other cells. I am now working in a multi-page user form, and I would
> > to
> > > do a similar action with text boxes.
> > >
> > > The formula is as follows:
> > > =INDEX('Catalog Listings'!$B$3:$D$223,MATCH(C30,'Catalog
> > > Listings'!$B$3:$B$223,0),1)
> > >
> > > So, the question is this. How do I INDEX/MATCH from a comboBox in a
> > > userform to several different text boxes (3 to be specific)?
> > >
> > > As always any advice is appreciated.
> > > Regards,
> > > Amber
> > >
> > >
> > > --
> > > Amber_D_Laws
> > >
> > ------------------------------------------------------------------------
> > > Amber_D_Laws's Profile:
> > http://www.excelforum.com/member.php...o&userid=30012
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=513312
> > >
>
> --
> Amber_D_Laws
> ------------------------------------------------------------------------
> Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
> View this thread: http://www.excelforum.com/showthread...hreadid=513312

--

Dave Peterson

Dave Peterson · 02-16-2006, 05:10 PM

And I might replace:
Textbox1.Value = res
with:

if iserror(res) then
textbox1.value = "No match" 'or whatever you want to see
else
textbox1.value = res
end if

Dave Peterson wrote:
>
> dim res as variant
>
> Amber_D_Laws wrote:
> >
> > Great! However, I need to how to define the varible "res"
> >
> > Tom Ogilvy Wrote:
> > > res = Application.INDEX(Range("Catalog Listings!$B$3:$D$223"), _
> > > Application.MATCH(Combobox1.Value, Range( _
> > > "Catalog Listings!$B$3:$B$223"),0),1)
> > >
> > > Textbox1.Value = res
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > >
> > > "Amber_D_Laws"
> > > <[email protected]>
> > > wrote in message
> > > news:[email protected]...
> > > >
> > > > In the original version of my quote module I was using the
> > > INDEX/MATCH
> > > > formula below in combination with a drop down box to populate
> > > several
> > > > other cells. I am now working in a multi-page user form, and I would
> > > to
> > > > do a similar action with text boxes.
> > > >
> > > > The formula is as follows:
> > > > =INDEX('Catalog Listings'!$B$3:$D$223,MATCH(C30,'Catalog
> > > > Listings'!$B$3:$B$223,0),1)
> > > >
> > > > So, the question is this. How do I INDEX/MATCH from a comboBox in a
> > > > userform to several different text boxes (3 to be specific)?
> > > >
> > > > As always any advice is appreciated.
> > > > Regards,
> > > > Amber
> > > >
> > > >
> > > > --
> > > > Amber_D_Laws
> > > >
> > > ------------------------------------------------------------------------
> > > > Amber_D_Laws's Profile:
> > > http://www.excelforum.com/member.php...o&userid=30012
> > > > View this thread:
> > > http://www.excelforum.com/showthread...hreadid=513312
> > > >
> >
> > --
> > Amber_D_Laws
> > ------------------------------------------------------------------------
> > Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
> > View this thread: http://www.excelforum.com/showthread...hreadid=513312
>
> --
>
> Dave Peterson

--

Dave Peterson

**Amber_D_Laws** · 02-16-2006, 05:55 PM

Thanks Tom,

I am glad that you figured out what the bug was, but how does your solution fit into the code as a whole. Maybe I am just being dumb about this, but I don't see the equivilant spot to replace this latter suggestion into the code.

Sorry I'm being dense,
Amber

Originally Posted by Tom Ogilvy

It was the spaces in names that was the problem - at least it worked when I
adjusted for that. If I had used

Worksheets("Catalog Listings").Range("B3:D223")

it would have been fine. But apparently when thrown into a range argument
as a single string, it needs the single quotes.

--
Regards,
Tom Ogilvy

"Toppers" <[email protected]> wrote in message
news:[email protected]...
> Amber,
> I tried Tom's code (on Eric's "problem" that you replied
to)
> and it worked fine so this suggests a typo on your part.
>
> Res will default to variant if you don't DIM it ( and I didn't).
>
> "Amber_D_Laws" wrote:
>
> >
> > I tried defining as:
> >
> > Dim res As String
> > and
> > Dim res As Variant
> >
> > both return the following error:
> >
> > "Run-time error '1004':
> > Method 'Range' of object '_Global' failed
> >
> > I'm I doing something wrong, or does the code contain a typo?
> >
> > Amber

> >
> > Amber_D_Laws Wrote:
> > > Great! However, I need to how to define the varible "res"
> >
> >
> > --
> > Amber_D_Laws
> > ------------------------------------------------------------------------
> > Amber_D_Laws's Profile:
http://www.excelforum.com/member.php...o&userid=30012
> > View this thread:
http://www.excelforum.com/showthread...hreadid=513312
> >
> >

**Amber_D_Laws** · 02-16-2006, 05:55 PM

Great Idea Dave!!
I will incorporate this into the code.

Originally Posted by Dave Peterson

And I might replace:
Textbox1.Value = res
with:

if iserror(res) then
textbox1.value = "No match" 'or whatever you want to see
else
textbox1.value = res
end if

Dave Peterson wrote:
>
> dim res as variant
>
> Amber_D_Laws wrote:
> >
> > Great! However, I need to how to define the varible "res"
> >
> > Tom Ogilvy Wrote:
> > > res = Application.INDEX(Range("Catalog Listings!$B$3:$D$223"), _
> > > Application.MATCH(Combobox1.Value, Range( _
> > > "Catalog Listings!$B$3:$B$223"),0),1)
> > >
> > > Textbox1.Value = res
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > >
> > > "Amber_D_Laws"
> > > <[email protected]>
> > > wrote in message
> > > news:[email protected]...
> > > >
> > > > In the original version of my quote module I was using the
> > > INDEX/MATCH
> > > > formula below in combination with a drop down box to populate
> > > several
> > > > other cells. I am now working in a multi-page user form, and I would
> > > to
> > > > do a similar action with text boxes.
> > > >
> > > > The formula is as follows:
> > > > =INDEX('Catalog Listings'!$B$3:$D$223,MATCH(C30,'Catalog
> > > > Listings'!$B$3:$B$223,0),1)
> > > >
> > > > So, the question is this. How do I INDEX/MATCH from a comboBox in a
> > > > userform to several different text boxes (3 to be specific)?
> > > >
> > > > As always any advice is appreciated.
> > > > Regards,
> > > > Amber
> > > >
> > > >
> > > > --
> > > > Amber_D_Laws
> > > >
> > > ------------------------------------------------------------------------
> > > > Amber_D_Laws's Profile:
> > > http://www.excelforum.com/member.php...o&userid=30012
> > > > View this thread:
> > > http://www.excelforum.com/showthread...hreadid=513312
> > > >
> >
> > --
> > Amber_D_Laws
> > ------------------------------------------------------------------------
> > Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
> > View this thread: http://www.excelforum.com/showthread...hreadid=513312
>
> --
>
> Dave Peterson

--

Dave Peterson

**Amber_D_Laws** · 02-16-2006, 05:59 PM

Toppers...long time no see!
I'm glad to know that the code worked, and that I could help Eric; even if by proxy.

See ya' around,
Amber

Originally Posted by Toppers

Amber,
I tried Tom's code (on Eric's "problem" that you replied to)
and it worked fine so this suggests a typo on your part.

Res will default to variant if you don't DIM it ( and I didn't).

"Amber_D_Laws" wrote:

>
> I tried defining as:
>
> Dim res As String
> and
> Dim res As Variant
>
> both return the following error:
>
> "Run-time error '1004':
> Method 'Range' of object '_Global' failed
>
> I'm I doing something wrong, or does the code contain a typo?
>
> Amber

>
> Amber_D_Laws Wrote:
> > Great! However, I need to how to define the varible "res"
>
>
> --
> Amber_D_Laws
> ------------------------------------------------------------------------
> Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
> View this thread: http://www.excelforum.com/showthread...hreadid=513312
>
>

**Amber_D_Laws** · 02-17-2006, 10:53 AM

*******bump*******

**Amber_D_Laws** · 02-17-2006, 12:19 PM

Well, there you go. I just realized what you meant about the single quotes. I put them in and it works like a dream! Thanks again Tom, you are a life saver as usual!

Originally Posted by Tom Ogilvy

Private Sub ComboBox1_Click()
Dim res As Variant
res = Application.Index(Range("'Catalog Listings'!$B$3:$D$223"), _
Application.Match(ComboBox1.Value, Range( _
"'Catalog Listings'!$B$3:$B$223"), 0), 2)
TextBox1.Value = res
End Sub

worked fine for me. (guess you need the single quotes for the range
argument)

--
Regards,
Tom Ogilvy

"Amber_D_Laws" <[email protected]>
wrote in message
news:[email protected]...
>
> I tried defining as:
>
> Dim res As String
> and
> Dim res As Variant
>
> both return the following error:
>
> "Run-time error '1004':
> Method 'Range' of object '_Global' failed
>
> I'm I doing something wrong, or does the code contain a typo?
>
> Amber

>
> Amber_D_Laws Wrote:
> > Great! However, I need to how to define the varible "res"
>
>
> --
> Amber_D_Laws
> ------------------------------------------------------------------------
> Amber_D_Laws's Profile:
http://www.excelforum.com/member.php...o&userid=30012
> View this thread: http://www.excelforum.com/showthread...hreadid=513312
>