Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generic Name for ActiveRange in VLookup
Hi there
Can anyone assist here? I need the range name/reference in the vlookup at the bottom of this spiel to change to reflect the appropriate "ListFillRange", which itself appears in a list box but changes dependent on the "case" selected: ***Part 1: A selection is made in a drop down list. Dependant on this selection, the corresponding set of values appears in a list box (as per below) Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row = 17 And Target.Column = 6 Then Select Case Target.Value Case "A" DialogSheets("ACCCode").ListBoxes("List Box 4").ListFillRange = "'A Codes'!ACodes" Case "B" DialogSheets("ACCCode").ListBoxes("List Box 4").ListFillRange = "'B Codes'!BCodes" Case "C" DialogSheets("ACCCode").ListBoxes("List Box 4").ListFillRange = "'C Codes'!CCodes" Case "Please Select" DialogSheets("ACCCode").ListBoxes("List Box 4").ListFillRange = "" End Select End If End Sub ***Part 2: A value in the list box is then selected and on clicking the ok button, a code that represents that value is returned to the active cell (eg: "Case A" ListFillRange contains "Apples - Green", the code for which is 511023). The vlookup below returns 511023 in the active cell. Sub DialogOK1() ListIndex = DialogSheets("ACCCode").ListBoxes("List box 4").ListIndex mytext = Application.WorksheetFunction.VLookup((DialogSheet s("ACCCode").ListBoxes("List box 4").List(ListIndex)), Range("what do I put here"), 2, False) ActiveCell.FormulaR1C1 = mytext What I want to do is tell the vlookup to refer to whichever range is active dependent on the initial selection in part 1 (eg: Case A, Case B etc because they all contain different codes)...i don't want to have to name all of the ranges indiviually (and I don't think it would actually work anyway).. If you can understand my poor description of this query, you're already doing well..and I welcome any ideas kind regards Sue |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generic Name for ActiveRange in VLookup
Dim rng as range
.. . . set rng = Range(DialogSheets("ACCCode").Listboxes("List box 4").ListFillRange) mytext = Application.WorksheetFunction.VLookup(( _ DialogSheets("ACCCode").ListBoxes("List box 4").List(ListIndex)), rng, 2, False) ActiveCell.FormulaR1C1 = mytext -- Regards, Tom Ogilvy "Sue" wrote in message om... Hi there Can anyone assist here? I need the range name/reference in the vlookup at the bottom of this spiel to change to reflect the appropriate "ListFillRange", which itself appears in a list box but changes dependent on the "case" selected: ***Part 1: A selection is made in a drop down list. Dependant on this selection, the corresponding set of values appears in a list box (as per below) Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row = 17 And Target.Column = 6 Then Select Case Target.Value Case "A" DialogSheets("ACCCode").ListBoxes("List Box 4").ListFillRange = "'A Codes'!ACodes" Case "B" DialogSheets("ACCCode").ListBoxes("List Box 4").ListFillRange = "'B Codes'!BCodes" Case "C" DialogSheets("ACCCode").ListBoxes("List Box 4").ListFillRange = "'C Codes'!CCodes" Case "Please Select" DialogSheets("ACCCode").ListBoxes("List Box 4").ListFillRange = "" End Select End If End Sub ***Part 2: A value in the list box is then selected and on clicking the ok button, a code that represents that value is returned to the active cell (eg: "Case A" ListFillRange contains "Apples - Green", the code for which is 511023). The vlookup below returns 511023 in the active cell. Sub DialogOK1() ListIndex = DialogSheets("ACCCode").ListBoxes("List box 4").ListIndex mytext = Application.WorksheetFunction.VLookup((DialogSheet s("ACCCode").ListBoxes("Li st box 4").List(ListIndex)), Range("what do I put here"), 2, False) ActiveCell.FormulaR1C1 = mytext What I want to do is tell the vlookup to refer to whichever range is active dependent on the initial selection in part 1 (eg: Case A, Case B etc because they all contain different codes)...i don't want to have to name all of the ranges indiviually (and I don't think it would actually work anyway).. If you can understand my poor description of this query, you're already doing well..and I welcome any ideas kind regards Sue |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generic Name for ActiveRange in VLookup
Hi Tom
Thanks for that - knew it was going to be easier than I was going to make it.. Will give it a whirl... Sue *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Generic Macro | Excel Discussion (Misc queries) | |||
Define a generic name | Excel Worksheet Functions | |||
If statement with generic value | Excel Worksheet Functions | |||
Make name Generic | Excel Programming | |||
Generic Macro | Excel Programming |