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

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

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!