Excel - Change cell formula from nested IFs to ??

So I have this ugly formula on a bunch of rows that looks like...

Code:

---

= (H23*IF(D23="YT",0.39,IF(D23="M/C",0.4,IF(D23="KK",0.45,IF(D23="M",0.71,0)))))

---

and I want to use VBA and change it over to a Select Case type of thing. I've never really worked with VBA before. What event(s) would I used, and so forth? Who wants to help me out? I'll send you five teeth for your trouble.

:afrog:

I need more info on what type of event you need to call this from. so I just made it a public function that can be
called from any sheet or event. Place it in a Module and we shall see where it needs to be called from best.

VB Code:

Public Function crptcblade() As Double
    Dim dblValue As Double
    Select Case D23
        Case "YT"
            dblValue = 0.39
        Case "M/C"
            dblValue = 0.4
        Case "KK"
            dblValue = 0.45
        Case "M"
            dblValue = 0.71
        Case Else
            dblValue = 0
    End Select
    'H23 equals - Workbooks("Book1").Sheets("Sheet1").Cells(23, 8).Value 
    crptcblade = Workbooks("Book1").Sheets("Sheet1").Cells(23, 8).Value * dblValue
End Function

When can I expect my 5 teeth? Are they molars or fangs? :p

Edit: Dont you mean 7 teeth?

Quote:

---

Originally Posted by RobDog888

I need more info on what type of event you need to call this from.

---

It would need to be a replacement for a formula in a range of cells. Basically, each cell in column K has that formula in it, but new values will be getting added regularly, and I don't want to have to maintain that big IF statement.

The only event I found was Sheet_Calculate, but I don't know how to figure out which cell has changed in order to update it.


As for the teeth, please transfer $458 for s/h to my Paypal and they are as good as yours. :afrog:

Resolved by me. I figured out how to link a function in a module to the formula.

So I'll keep the teeth.

:evil:

hi,

can you post how you achieved the resolution?

I would be interested to know.

Thnx :thumb:

Quote:

---

Originally Posted by crptcblade

Resolved by me. I figured out how to link a function in a module to the formula.

So I'll keep the teeth.

:evil:

---

Geez, you welch on the teeth and you dont want to share the solution. Boooo *SLAP* :lol:

Should I give you -Teeth then :lol: :p ;)

Quote:

---

Originally Posted by Br1an_g

hi,

can you post how you achieved the resolution?

I would be interested to know.

Thnx :thumb:

---

It actually turned out to be pretty easy. Add a module, and make a public function.

Code:

---

Public Function ReturnSomeValue(CellValue) As Double
  ReturnSomeValue = Val(CellValue) * 2.24
 End Function

---

Then make the formula in the cell "=ReturnSomeValue(H4)" and it should work fine. I guess it gets a little hinky passing ranges of cells in, I only needed to worry about one cell at a time.