INDEX/MATCH formula in VBA to populate text boxes

  • Thread starter Thread starter Amber_D_Laws
  • Start date Start date
A

Amber_D_Laws

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
 
Above I stated that it was 3 text boxes would need the formula. Actuall
two will use the INDEX/MATCH, and one will use a simple cell*cell*cel
formula. I also need help with that one, but I assume the answer wil
be much simpler
 
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]...
 
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 :)
 
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
 
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).
 
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.
 
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
 
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 :)
 
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 :cool:
 
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!:cool:
 
Back
Top