Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba range in vlookup
Hello,
I am building a user form and trying to get a value by using Vlookup. However, I get an error in vba once it gets to the Select Case portion of the module. This SELECT CASE is trying to get the range for my vlookup formula. How it works is depending on what ComboBox value is in the cboPrgType will depend on what named range it will look at in the formula; all the named ranges are in the spread sheet. I used the Select Case vba formula to determine what range it will use. TableRange is the varible used in the vba Vlookup formula that will determin the named range. Here is my code: Private Sub CommandButton1_Click() Dim LTV As Double Dim CLTV As Double Dim TableRange As Range Dim ColumnNum As Integer Dim CurrRate As String Dim Price As Long If txtLoanAmtOne = vbNullString Then MsgBox "Please enter a loan amount" Exit Sub End If If txtSalesValue = vbNullString Then MsgBox "Please enter a Sales/Appraised value" Exit Sub End If LTV = CDbl(txtLoanAmtOne.Text) / CDbl(txtSalesValue.Text) If txtLoanAmtTwo = vbNullString Then CLTV = LTV Else CLTV = (CDbl(txtLoanAmtOne.Text) + CDbl(txtLoanAmtTwo.Text)) / CDbl(txtSalesValue.Text) End If If LTV 0.97 Then MsgBox "Sorry, LTV is greater than allowable" Exit Sub End If If CLTV 1 Then MsgBox "Sorry, CLTV is above Max CLTV" Exit Sub End If txtLtvValue.Text = CDbl(LTV) * 100 txtCltvValue.Text = CDbl(CLTV) * 100 CurrRate = cboRate.Value Select Case cboPrgType.Value Case "MM30YFRates" TableRange = MM30YFTable Case "MM20YFRates" TableRange = MM20YFTable Case "MM15YFRates" TableRange = MM15YFTable Case "MM26LRates" TableRange = MM26LTable Case "MM36LRates" TableRange = MM36LTable Case "MM56LRates" TableRange = MM56LTable Case "MMS30YFRates" TableRange = MMS30YFTable Case "MMS15YFRates" TableRange = MMS15YFTable Case "MMS20YFRates" TableRange = MMS20YFTable Case "MMS3015YBRates" TableRange = MMS3015YBTable End Select Select Case cboAmort.Value Case "21" ColumnNum = 4 Case "36" ColumnNum = 5 Case "45" ColumnNum = 6 End Select Price = Application.VLookup(CurrRate, Range(TableRange), ColumnNum, False) txtBuyPrice.Text = CDbl(Price) End Sub Is my SELECT CASE set up correctly? Please help |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba range in vlookup
Assuming MM30YFTable is a named range then your Select statement should
probably look something like: Select Case cboPrgType.Value Case "MM30YFRates" Set TableRange = Range("MM30YFTable") 'etc Your vlookup should then read: Price = Application.VLookup(CurrRate, TableRange, ColumnNum, False) Hope this helps Rowan wrote in message oups.com... Hello, I am building a user form and trying to get a value by using Vlookup. However, I get an error in vba once it gets to the Select Case portion of the module. This SELECT CASE is trying to get the range for my vlookup formula. How it works is depending on what ComboBox value is in the cboPrgType will depend on what named range it will look at in the formula; all the named ranges are in the spread sheet. I used the Select Case vba formula to determine what range it will use. TableRange is the varible used in the vba Vlookup formula that will determin the named range. Here is my code: Private Sub CommandButton1_Click() Dim LTV As Double Dim CLTV As Double Dim TableRange As Range Dim ColumnNum As Integer Dim CurrRate As String Dim Price As Long If txtLoanAmtOne = vbNullString Then MsgBox "Please enter a loan amount" Exit Sub End If If txtSalesValue = vbNullString Then MsgBox "Please enter a Sales/Appraised value" Exit Sub End If LTV = CDbl(txtLoanAmtOne.Text) / CDbl(txtSalesValue.Text) If txtLoanAmtTwo = vbNullString Then CLTV = LTV Else CLTV = (CDbl(txtLoanAmtOne.Text) + CDbl(txtLoanAmtTwo.Text)) / CDbl(txtSalesValue.Text) End If If LTV 0.97 Then MsgBox "Sorry, LTV is greater than allowable" Exit Sub End If If CLTV 1 Then MsgBox "Sorry, CLTV is above Max CLTV" Exit Sub End If txtLtvValue.Text = CDbl(LTV) * 100 txtCltvValue.Text = CDbl(CLTV) * 100 CurrRate = cboRate.Value Select Case cboPrgType.Value Case "MM30YFRates" TableRange = MM30YFTable Case "MM20YFRates" TableRange = MM20YFTable Case "MM15YFRates" TableRange = MM15YFTable Case "MM26LRates" TableRange = MM26LTable Case "MM36LRates" TableRange = MM36LTable Case "MM56LRates" TableRange = MM56LTable Case "MMS30YFRates" TableRange = MMS30YFTable Case "MMS15YFRates" TableRange = MMS15YFTable Case "MMS20YFRates" TableRange = MMS20YFTable Case "MMS3015YBRates" TableRange = MMS3015YBTable End Select Select Case cboAmort.Value Case "21" ColumnNum = 4 Case "36" ColumnNum = 5 Case "45" ColumnNum = 6 End Select Price = Application.VLookup(CurrRate, Range(TableRange), ColumnNum, False) txtBuyPrice.Text = CDbl(Price) End Sub Is my SELECT CASE set up correctly? Please help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba range in vlookup
Thanks for your response Rowan. I reconfigured all Sets in the Select
Case but I still get an error messege: "Run-time error '91': Object variable or With block variable not set." This happens at line TableRange = Range("MM30YFTable"); this is if I choose my Case value to select this range. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba range in vlookup
As Rowan said, that line should read
Set TableRange = Range("MM30YFTable") -- HTH Bob Phillips (remove nothere from email address if mailing direct) wrote in message oups.com... Thanks for your response Rowan. I reconfigured all Sets in the Select Case but I still get an error messege: "Run-time error '91': Object variable or With block variable not set." This happens at line TableRange = Range("MM30YFTable"); this is if I choose my Case value to select this range. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba range in vlookup
Sorry, forgot the Set part and thanks, that helped alot. However, now I
am now getting this error message: Run-time error '13': Type mismatch The vlookup formula is also highlighted in yellow. When my currsor hovers over each of the arguments in the vlookup formula, it displays the correct value for each variable in the formula except for TableRange. It dose not display anything for it. Also, when I hover over the top of the Select Case statement over the cboPrgType.Value, it gives me the correct chosen value of "MM30YFRates". Once I hover over any of the Sets in the statement, I don't get any values until I get to the last Set. When I hover over the "= Range("MMS3015YBTable")" , which is the last set, the off-white box states Range("MMS3015YBTable") = <Method range 'of object'_Global' failed How can I fix this? I really appreciate your help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup range | Excel Discussion (Misc queries) | |||
Range Vlookup | Excel Worksheet Functions | |||
Using VLOOKUP with a 3D range | Excel Worksheet Functions | |||
vlookup range changes | Excel Worksheet Functions | |||
Vlookup - Range | Excel Discussion (Misc queries) |