+ Reply to Thread
Results 1 to 15 of 15

INDEX/MATCH formula in VBA to populate text boxes

  1. #1
    Forum Contributor
    Join Date
    12-30-2005
    Location
    Mobile, AL
    Posts
    102

    Arrow INDEX/MATCH formula in VBA to populate text boxes

    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

  2. #2
    Forum Contributor
    Join Date
    12-30-2005
    Location
    Mobile, AL
    Posts
    102

    Clarification

    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.

  3. #3
    Tom Ogilvy
    Guest

    Re: INDEX/MATCH formula in VBA to populate text boxes

    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" <Amber_D_Laws.23c4wm_1140115201.9149@excelforum-nospam.com>
    wrote in message
    news:Amber_D_Laws.23c4wm_1140115201.9149@excelforum-nospam.com...
    >
    > 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
    >




  4. #4
    Forum Contributor
    Join Date
    12-30-2005
    Location
    Mobile, AL
    Posts
    102
    Great! However, I need to how to define the varible "res"


    Quote 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" <Amber_D_Laws.23c4wm_1140115201.9149@excelforum-nospam.com>
    wrote in message
    news:Amber_D_Laws.23c4wm_1140115201.9149@excelforum-nospam.com...
    >
    > 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
    >

  5. #5
    Forum Contributor
    Join Date
    12-30-2005
    Location
    Mobile, AL
    Posts
    102
    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

    Quote Originally Posted by Amber_D_Laws
    Great! However, I need to how to define the varible "res"

  6. #6
    Toppers
    Guest

    Re: INDEX/MATCH formula in VBA to populate text boxes

    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
    >
    >


  7. #7
    Tom Ogilvy
    Guest

    Re: INDEX/MATCH formula in VBA to populate text boxes

    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" <Amber_D_Laws.23c7om_1140118802.9768@excelforum-nospam.com>
    wrote in message
    news:Amber_D_Laws.23c7om_1140118802.9768@excelforum-nospam.com...
    >
    > 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
    >




  8. #8
    Tom Ogilvy
    Guest

    Re: INDEX/MATCH formula in VBA to populate text boxes

    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" <Toppers@discussions.microsoft.com> wrote in message
    news:03E8E684-C140-412E-99A0-2401723BEF52@microsoft.com...
    > 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
    > >
    > >




  9. #9
    Dave Peterson
    Guest

    Re: INDEX/MATCH formula in VBA to populate text boxes

    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"
    > > <Amber_D_Laws.23c4wm_1140115201.9149@excelforum-nospam.com>
    > > wrote in message
    > > news:Amber_D_Laws.23c4wm_1140115201.9149@excelforum-nospam.com...
    > > >
    > > > 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

  10. #10
    Dave Peterson
    Guest

    Re: INDEX/MATCH formula in VBA to populate text boxes

    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"
    > > > <Amber_D_Laws.23c4wm_1140115201.9149@excelforum-nospam.com>
    > > > wrote in message
    > > > news:Amber_D_Laws.23c4wm_1140115201.9149@excelforum-nospam.com...
    > > > >
    > > > > 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

  11. #11
    Forum Contributor
    Join Date
    12-30-2005
    Location
    Mobile, AL
    Posts
    102
    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



    Quote 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" <Toppers@discussions.microsoft.com> wrote in message
    news:03E8E684-C140-412E-99A0-2401723BEF52@microsoft.com...
    > 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
    > >
    > >

  12. #12
    Forum Contributor
    Join Date
    12-30-2005
    Location
    Mobile, AL
    Posts
    102
    Great Idea Dave!!
    I will incorporate this into the code.



    Quote 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"
    > > > <Amber_D_Laws.23c4wm_1140115201.9149@excelforum-nospam.com>
    > > > wrote in message
    > > > news:Amber_D_Laws.23c4wm_1140115201.9149@excelforum-nospam.com...
    > > > >
    > > > > 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

  13. #13
    Forum Contributor
    Join Date
    12-30-2005
    Location
    Mobile, AL
    Posts
    102
    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


    Quote 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
    >
    >

  14. #14
    Forum Contributor
    Join Date
    12-30-2005
    Location
    Mobile, AL
    Posts
    102
    *******bump*******

  15. #15
    Forum Contributor
    Join Date
    12-30-2005
    Location
    Mobile, AL
    Posts
    102

    Thumbs up It WORKS!!!!!!! Yippie!

    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!

    Quote 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" <Amber_D_Laws.23c7om_1140118802.9768@excelforum-nospam.com>
    wrote in message
    news:Amber_D_Laws.23c7om_1140118802.9768@excelforum-nospam.com...
    >
    > 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
    >

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1