|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
INDEX/MATCH formula in VBA to populate text boxes
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 :) Tom Ogilvy Wrote: 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 -- 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 |
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 |
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 :cool: 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 |
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 |
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!:cool: 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 |
| All times are GMT +1. The time now is 12:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com