code for lookup,& triggers.

  • Thread starter Thread starter derek
  • Start date Start date
D

derek

Excel 2000.
Hi, Ive just no idea on how to tackle this let alone code
it. I've simplified the example but with your initial help
I can expand it to complete the whole task.
User adds data to A1:B10 a row at a time. Required
answers in c1:c10
User can enter a number from 1 to 10 in A1 then goto B1
and enter a number from 1 to 100. When the number is
enterd in B1 I would like to; use the number in A1 to goto
the correct named range and look up a value assosiated
with B1.
Example. If A1 contains 5 and B1 contains 20, when 20 is
enterd into B1 do a lookup in a range name "Five" using
the value in B1 to return a value to C1. This process to
be repeated whenever data is entered into a subsequent
rows. The numbers 1 to 10 that could be entered into
colum A represents ten named ranges.

TIA, derek..
 
I think that this works:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRngNames(1 To 10) As String
Dim myLookatRng As Range
Dim testRng As Range
Dim res As Variant

On Error GoTo errHandler:

Set myLookatRng = Me.Range("a1:b10")

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, myLookatRng) Is Nothing Then Exit Sub

If Application.CountA(Me.Range("a" & Target.Row).Resize(1, 2)) <> 2 Then
Exit Sub
End If

myRngNames(1) = "one"
myRngNames(2) = "two"
myRngNames(3) = "three"
myRngNames(4) = "four"
myRngNames(5) = "five"
myRngNames(6) = "six"
myRngNames(7) = "seven"
myRngNames(8) = "eight"
myRngNames(9) = "nine"
myRngNames(10) = "ten"

Set testRng = Nothing
On Error Resume Next
Set testRng = ThisWorkbook.Names _
(myRngNames(Me.Range("a" & Target.Row).Value)).RefersToRange
On Error GoTo errHandler:

If testRng Is Nothing Then
Me.Range("c" & Target.Row).Value = CVErr(xlErrRef)
Exit Sub
End If

res = Application.VLookup(Me.Range("b" & Target.Row).Value, testRng, 2, 0)

Application.EnableEvents = False
If IsError(res) Then
Me.Range("C" & Target.Row).Value = CVErr(xlErrNA)
Else
Me.Range("c" & Target.Row).Value = res
End If

errHandler:
Application.EnableEvents = True
End Sub

Right click on the worksheet that should have this behavior and select view
code. Paste this in.

Try it out.

(I retrieved the 2nd column in each range--You didn't say what column to bring
back.)
 
Oops.

Use this section instead of the original:

If testRng Is Nothing Then
Application.EnableEvents = False
Me.Range("c" & Target.Row).Value = CVErr(xlErrRef)
Application.EnableEvents = True
Exit Sub
End If

(I added the .value = cverr(xlerrref) later and forgot to turn off/on the
..enableevents.)
 
And maybe just a worksheet formula:

=VLOOKUP(B1,INDIRECT(CHOOSE(A1,"one","two","three","four",
"five","six","seven","eight","nine","ten")),2,FALSE)

All in one cell c1 (and drag down).
 
Back
Top