vba range in vlookup

[isauror]

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

 

[Rowan Drummond]

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

 

[isauror]

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.

 

[Bob Phillips]

As Rowan said, that line should read

Set TableRange = Range("MM30YFTable")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[isauror]

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