Search code examples
excelvbacell

Excel VBA - Change cell value via another cell?

So VBA is completely new to me. I come from a C# background. Currently creating an order form, whereby I can input the total amount I require of an object and then the price is calculated in a different cell.

However I also want the price to change based off amount thresholds for that object.

Say for example 0 - 100 will cost £2.50 so I expect the answer to be anywhere within that range is multiplied by 2.50. Meanwhile if the amount exceeds 100 and becomes 120 I want the object price to now reflect £2.30 and proceed to multiple the 120 by £2.30.

I've noticed a few tutorials on line but they don't exactly explain how I might be able to achieve the above. Wondering if anyone can point me in the right direction?

Solution

  • Non VBA Soluion

    If you build a table with the bottom and upper thresholds for a price, you can simply use a VLOOKUP and return the approximate match.

    In photo, Column C is the output from the equation that is shown in Column D

    enter image description here

    VBA Solution

    You can also use a simple UDF. Paste the code inside a Module and then you can call the function PRICEINDX from a cell just like any other equation. You can either manually type in a value like PRICEINDX(164) or select a cell that has the value to be tested like PRICEINDX(A1)

    You can also set up more complex thresholds easily by using Select Case

    Option Explicit

Public Function PRICEINDX(Target As Double) As Double

Dim ans As Double

Select Case Target
    Case 0 To 100
        ans = 2.5
    Case 101 To 200
        ans = 2.3
    Case 201 To 300
        ans = 2.1
    Case Is > 300
        ans = 2
End Select

PRICEINDX = ans

End Function