| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -- 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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
INDEX/MATCH formula in VBA to populate text boxes
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 -- Amber_D_Law ----------------------------------------------------------------------- Amber_D_Laws's Profile: http://www.excelforum.com/member.php...fo&userid=3001 View this thread: http://www.excelforum.com/showthread.php?threadid=51331 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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" wrote in message news:Amber_D_Laws.23c4wm_1140115201.9149@excelforu m-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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
INDEX/MATCH formula in VBA to populate text boxes
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" wrote in message news:Amber_D_Laws.23c4wm_1140115201.9149@excelforu m-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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
INDEX/MATCH formula in VBA to populate text boxes
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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" 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). "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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
INDEX/MATCH formula in VBA to populate text boxes
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  Toppers Wrote: 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 ------------------------------------------------------------------------ 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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" wrote in message news:Amber_D_Laws.23c7om_1140118802.9768@excelforu m-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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
INDEX/MATCH formula in VBA to populate text boxes
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! Tom Ogilvy Wrote: 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" wrote in message news:Amber_D_Laws.23c7om_1140118802.9768@excelforu m-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 -- 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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" wrote in message news:Amber_D_Laws.23c4wm_1140115201.9149@excelforu m-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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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" wrote in message news:Amber_D_Laws.23c4wm_1140115201.9149@excelforu m-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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
INDEX/MATCH formula in VBA to populate text boxes
Great Idea Dave!! I will incorporate this into the code. ;) Dave Peterson Wrote: 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" wrote in message news:Amber_D_Laws.23c4wm_1140115201.9149@excelforu m-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 -- 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
INDEX/MATCH formula in VBA to populate text boxes
*******bump******* -- 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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| index / lookup / match / text formula | Excel Discussion (Misc queries) | |||
| index / lookup / match / text formula | Excel Discussion (Misc queries) | |||
| index / lookup / match / text formula | Excel Discussion (Misc queries) | |||
| index / lookup / match / text formula | Excel Discussion (Misc queries) | |||
| Keep text color from a Index Match Formula | Excel Programming | |||
![Amber_D_Laws[_73_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
