Display value based off of letter in specific cell.

[Talk_4]

I would like to display a certain value in a cell based on two conditions:

1) If L11 = "P," "K," "C," or "F" then add 0, 1, 2 or 3 accordingly to Column A,

2) From what's being displayed in column B

Here is my formula in a VBA Module:

Function OverStock(L As Range, B As Range)
If L11 = "P" Then
OverStock = B
ElseIf L11 = "K" Then
OverStock = B + 1
ElseIf L11 = "C" Then
OverStock = B + 2
ElseIf L11 = "F" Then
OverStock = B + 3
End If
End Function

[walruseggman]

Please put your code in

Please Login or Register  to view this content.

[Talk_4]

how would this then be put into the empty cell where the value needs to be?

This was my previous formulations to get the desired value:
=IF(L11="P",B37,IF(L11="K",B37+1,IF(L11="C",B37+2,IF(L11="F",B37+3,""))))
Going down the list. My problem is when i try to drag the cell value down 30 rows, it changes the L11 value to L12, L13, etc.

So, I figured, if i'd just create my own variable in VBA, i can avoid this issue.

Just to give you an idea of my objective.

[walruseggman]

Well you can avoid the issue you mentioned by just adding a dollar sign before the 11 in L11. This changes it away from a relative reference.

Like this: =IF(L$11="P",B37,IF(L$11 etc. etc. etc.

But let me know if you still want VBA code.

[Talk_4]

Your a genious, I feel so stupid right now. lol.

Yes, please also provide me with the vba formatting, I believe by looking at expert coding like yours, i'll be able to gradually improve my own.

[walruseggman]

Here you go. Just adjust the rows you want output on (in my code it's rows 37 through 67).

Then every time you change the letter in L11, this code will run.

Please Login or Register  to view this content.

But personally in this case, i think a formula is a better option.