#### rca

##### Board Regular
Hello Everyone,

I have the following code:

___________________________________________
Function getFee(Symbol, Location, TransactionDate, Quantity, TransactionType)

If TransactionType = "Close Out" Then
getFee = Abs(Quantity) * 0
End If

'Evaluate the 1st symbol by Location and TransactionDate
If Symbol = "A" Or Symbol = "B" Then

Select Case Location
Case "New York"
Select Case TransactionDate
Case DateValue("02/17/05") To DateValue("09/30/05")
getFee = Abs(Quantity) * 0.3
Case DateValue("10/01/05") To DateValue("03/15/15")
getFee = Abs(Quantity) * 0.45
Case Else
getFee = "???"
End Select

Case Location = "Los Angeles"
Select Case TransactionDate
Case DateValue("02/17/05") To DateValue("03/15/15")
getFee = Abs(Quantity) * 0.3
Case Else
getFee = "???"
End Select

End Select
End If

'Evaluate the 2nd symbol by Location and TransactionDate
If Symbol = "C" Then

Select Case Location
Case "New York"
Select Case TransactionDate
Case DateValue("02/17/05") To DateValue("03/15/15")
getFee = Abs(Quantity) * 0.9
Case Else
getFee = "???"
End Select

Case Location = "Los Angeles"
Select Case TransactionDate
Case DateValue("02/17/05") To DateValue("03/15/15")
getFee = Abs(Quantity) * 0.85
Case Else
getFee = "???"
End Select

End Select
End If

End Function
________________________________________

The code above returns a fee based on the location and date of the transaction. Here's the problem:

The code is suppose to return ZERO as the fee when the transaction is a "Close Out". (I tell the code to multiply by 0, thereby rendering the result 0.)

I thought that this snippet of code would do that:
________________________________________
If TransactionType = "Close Out" Then
getFee = Abs(Quantity) * 0
End If
________________________________________

But, the code seems to 'skip' over this section of the code and returns a value regardless of whether the TransactionType is a 'Close Out' of not.

Can someone see what is wrong with the section of the code that is suppose to return the Fee as ZERO if the TransactionType is a 'Close Out'?

Thank you,
Rachel

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### Oorang

##### Well-known Member
Might be an issue where your users are putting in leading or trailing spaces and/or weird capitalization. Try this:
Code:
``````If Trim(StrConv(TransactionType, 2)) = "close out" Then
getFee = Abs(Quantity) * 0
End If``````

#### rca

##### Board Regular
Hi Oorang,

I tried your suggestion and that code still returns a non-zero value. I'm not sure why? It should definitely return a zero value and disregard the rest of the code. I'm not sure why it doesn't. Any other ideas?

Thanks,
Rach

#### Sharkie21

##### Active Member
Hrmm you can probably look at the values in debug mode to see what's the difference.

You might have a better chance with, but not sure was to lazy to see what the 2 means for strConv.

If Trim(LCase(TransactionType)) = "close out" Then

#### rca

##### Board Regular
Hi Sharkie,

Well, I tried your suggestion as well. The code still returns a non-zero value. It seems like it's just 'skipping' that piece of the code that's suppose to trap the 'Close Out' transaction types. It's sooo strange.

What am I doing wrong?

Thanks,
Rachel

#### Seti

##### Well-known Member
Maybe this?

If TransactionType = "Close Out" Then
getFee = Abs(Quantity) * 0
Exit Function
End If

#### rca

##### Board Regular
Thank you Seti! That works. What a silly mistake I made by not putting Exit Function!

I would like to thank all of you for your expert help!

Have a great day!
Rachel

Replies
2
Views
362
Replies
13
Views
519
Replies
9
Views
361
Replies
7
Views
572
Replies
3
Views
1K

1,190,958
Messages
5,983,843
Members
439,866
Latest member
jh3268

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back