Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Converted to VB
After days of running into Excel limitations I have resorted to trying to do
this in VB (definitely not my strength). I have too many nested conditionals for Excel to handle so I wanted to create a function in VB. I need to convert VLOOKUP(D50,INDIRECT(D50&"_Contract"),2,FALSE) to the VB equivalent. DLookup looks to be the best I could find, but one clear problem I forsee is DLookup is using text and my commision table is full of numbers. Most of the table on a different tab than the computations looks like this: Life LTC $MART H.O.M.E. G.O.O.D. M Funds REP 25% 10.00% 0.31% 0.31% 0.31% 30.00% SREP 35% 15.50% 0.36% 0.36% 0.36% 32.50% DIS 50% 20.00% 0.44% 0.44% 0.44% 35.00% DIV 60% 25.00% 0.57% 0.57% 0.57% 37.50% REG 70% 30.00% 0.83% 0.83% 0.83% 42.50% SREG 80% 35.00% 0.83% 0.83% 0.83% 47.50% RVP 95% 40.00% 1.23% 1.23% 1.25% 62.00% D51 is SREG. So, in E51 I am looking up what the Life Product compensation for a SREG when the customer pays an E19 premium. If E19, Life_Prem, was $100, then E51=VLOOKUP(D51,INDIRECT(D51&"_Contract"),2,FALSE) *(Life_Prem*12)*75%)*82.226%), where SREG_Contract is a named range from the above SREG through to 47.50% horizontally. SREG's commission is 80%; E51 should return 80%*100*12*75%=720 Your help is VERY appreciated! |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically it should be converted | Excel Discussion (Misc queries) | |||
I want 148:30 converted to 148.5 and I am an idiot | Excel Discussion (Misc queries) | |||
Answer to 2/29 gets converted to 2/1 | Excel Discussion (Misc queries) | |||
h:mm:ss AM + h:mm:ss AM converted to minutes? | New Users to Excel | |||
How can a value be converted to words | Excel Discussion (Misc queries) |