Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
code for lookup,& triggers.
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.. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
code for lookup,& triggers.
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.) derek wrote: 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.. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
code for lookup,& triggers.
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.) Dave Peterson wrote: 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.) derek wrote: 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.. -- Dave Peterson -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
code for lookup,& triggers.
And maybe just a worksheet formula:
=VLOOKUP(B1,INDIRECT(CHOOSE(A1,"one","two","three" ,"four", "five","six","seven","eight","nine","ten")),2,FALS E) All in one cell c1 (and drag down). Dave Peterson wrote: 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.) Dave Peterson wrote: 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.) derek wrote: 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.. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro triggers Virus Scanner | New Users to Excel | |||
How to activate triggers in Excel | Setting up and Configuration of Excel | |||
Triggers to buy/sell based on histogram | Excel Discussion (Misc queries) | |||
Macro that triggers off a combo box | Excel Discussion (Misc queries) | |||
Can I set an audio alert that triggers as Excel cell value chgs? | Excel Discussion (Misc queries) |