VBA: Inserting result to cell

[davenz]

Hi all,

I'm new here. And new to VBA.

I am working on an assignment for my studies and have created a hypothetical scenario of a bank offering different interest rates depending on the investment amount.
The process is currently;
Click a button, input box asks for deposit amount, click ok, msgbox shows the result (using a separate function to determine the rate).

What I can't work out how to do is have that same result not only show in the msgbox, but also be inserted into a specific cell.

'This is the function that determines the rate'
Function NewBankRate(Deposit)
    Select Case Deposit
    
    Case Is < 500
        NewBankRate = CVErr(xlErrValue)
    Case 500 To 4999
        NewBankRate = 0.0475
    Case 4999 To 9999
        NewBankRate = 0.055
    Case 9999 To 14999
        NewBankRate = 0.0625
    Case 14999 To 19999
        NewBankRate = 0.0675
    
    End Select
    
        
End Function
'This is the input box with resulting msgbox'
Sub NewBankSelect()
    
        
    Deposit = InputBox("Please enter the deposit amount: ", "New Bank Rate Calculator", "0")
    MsgBox "New Bank Rate is: " & NewBankRate(Deposit), , "New Bank Rate Calculator"
    
    
End Sub

As mentioned, it works perfectly but I want to get the result displayed in the msgbox inserted into a cell so the user doesn't have to do it manually for the next step.
I'm guessing you need to assign the end rate as a 'result' then assign the 'result' to a cell, but every time I try from referring to online resources it doesn't work.

Any help would be greatly appreciated. Would also appreciate a bit of an explanation so I can learn for next time.

 

[HenkDePoema]

For example Range("B3").Value = NewBankRate(Deposit)
Or, if you want it on another worksheet: Worksheets("Another worksheet").Range(G14).Value = NewBankRate(Deposit)

You could insert this between your lines 'Deposit = InputBox...' and 'MsgBox "...' (or even remove the last line).

 

[davenz]

Thank you so much! I was close when I last tried - just had it round the wrong way!

Thanks heaps!

 

[HenkDePoema]

You're welcome!