Generic Name for ActiveRange in VLookup

[Sue]

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((DialogSheets("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

 

[Tom Ogilvy]

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

 

[Sue Reeve]

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