Excel - Change cell formula from nested IFs to ??

[crptcblade]

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

= (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.

[RobDog888]

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.

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?

Edit: Dont you mean 7 teeth?

[crptcblade]

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.

[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:

[Br1an_g]

hi,

can you post how you achieved the resolution?

I would be interested to know.

Thnx

[RobDog888]

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*

Should I give you -Teeth then

[crptcblade]

hi,

can you post how you achieved the resolution?

I would be interested to know.

Thnx

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

 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.