Intermittent code issue when setting variable to cell value

[frank_AL]

I have a code module that calculates monthly commission values. Some of the salesmen have shared accounts where 2 salesmen service a common account. In the database this is indicated by the SLS code (Column B) having 2 salesmen's initials separated by "/".
When this happens each salesman receives 1/2 of the commission for that record.

For example salesman FWB shares an account with salesman SRB. The SLS code for these entries would be FWB/SRB.
So salesman FWB would have entries where he is the sole salesman along with entries for the shared account.

In my code I test for this by using the INSTR function to see if "/" exists in the SLS code: If INSTR(cells(crow, "B"),"/") > 0
I have been validating the code module by taking one specific month and manually calculating the 91 records.
When I run my code module I am getting a different answer.

I added a variable called slscode and populated with the SLS code: slscode = Cells(crow, "B").Value
I changed the INSTR test to: If(INSTR(slscode, "/") > 0
So I put a break in the code for the INSTR line so I can determine if the code is identifying that the "/" exists.
What I am seeing is that in a few instances the slscode variable is EMPTY rather than being populated with the correct SLS code.
When this happens the commission for that record is added to the monthly total without being divided by 2
This results in the monthly total being incorrect.

When the slscode variable is EMPTY, I manually rerun that line of code and the variable is then accurately updated to the correct SLS code.

I am scheduled to meet with the client Monday and really need some help to get this resolved.
Has anyone encountered a similar issue or does anyone have a suggestion for getting this resolved?

 

[steve the fish]

Your problem no doubt is because:

Cells(crow, "B").Value

has no sheet qualifier. Lets say 'crow' equals 1. Which cell B1 do you refer to? There is a cell B1 on every single sheet of every single workbook. You need to be telling the code which B1 you refer to. eg

Sheets("Sheet1").Cells(crow, "B").Value

or you may even need to add a workbook qualifier too.

 

[frank_AL]

Thank you Steve. That fixed the issue.