Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup problems
Hola todos!
I want to do a double or triple Vlookup. I want to do that by VBA. I use at this moment the program below. just need to have the nearest value, because I have a approximate valu for LongLeader = 0.12456 'Exemple the program look in the list. It look for Diameter, kind of tool and the length. But it will return th length of the supplier. For the exemple, it will be 0.125 instead o 0.12456. Set rng = [A1:A2000] For Each cel In rng If cel.Value = "Leader pin" Then If cel.Offset(, 1).Value = DiaLeader Then If cel.Offset(, 2).Value = LongLeader Then ' How can I program it t look for the nearest value in cel.offset(,3).value LongLeaderDME = cel.Offset(, 3).Value End If End If End If Next cel thanks for help and if you have something different (anothe programmation) it will be perfect to -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup problems
Try the code below: copy it all into a codemodule, and run TryNow.
HTH, Bernie MS Excel MVP Dim LongLeaderDME As Double Sub TryNow() GetData 0.25, 3.475 MsgBox LongLeaderDME End Sub Sub GetData(DiaLeader As Double, _ LongLeader As Double) Dim cel As Range Dim rng As Range Set rng = [A1:A2000] For Each cel In rng If cel.Value = "Leader pin" Then If cel.Offset(, 1).Value = DiaLeader Then If cel.Offset(, 2).Value LongLeader Then LongLeaderDME = cel.Offset(, 3).Value Exit For End If End If End If Next cel End Sub "zapatista66 " wrote in message ... Kind diameter length Price Leader pin 0.250 3.250 3$ Leader pin 0.250 3.750 4$ Leader pin 0.250 4.250 10$ Leader pin 0.750 4.750 15$ Leader pin 0.750 5.250 5$ Leader pin 0.750 5.750 7$ Socket 1 6.250 5$ Socket 1 6.750 2$ Socket 1 7.250 21$ LongLeaderDME = length So, I want to look first in kind, after in diameter and to return the length in the list. exemple: kind:Leader pin, diameter:0.250, and LongLeader:3.743 The program will return the line 2. Length=3.750 because is the nearest value (up) for LongLeader. Finally, I can have the price 4$. Is it possible ? --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup problems
I have to sort my data. If not, the nearest value will be false.
can I use something without sorting my data? I don't know why but it takes my last data. The program start at th end of range A1:A2000??? -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LOOKUP problems | Excel Worksheet Functions | |||
Problems with LOOKUP | Excel Worksheet Functions | |||
v-lookup problems | Excel Worksheet Functions | |||
Lookup problems | Excel Worksheet Functions | |||
lookup problems | Excel Worksheet Functions |