I have a excel vba macro that tries to examine records in rows of a worksheet. The macro runs correctly sometimes but other times the same macro will get either nulls or zero for the same results when examining the row data. Below is some of the code.
If Cells(myrow, CFRepDue).Value = “” Then ‘ if due date blank skip it else match on year
MyStr = Right((Cells(myrow, CFRepDue)), 4) ‘ Pick up last 4 digits of date field s/b
The above code examines a date field at row number myrow and column CFRepDue which is column 10. There is a date in this column and in excel the cell is defined as date, but sometimes this gives a result of null and other times, it works correctly by finding a non-null date.
In a similar manner, the following code
LifeExp = Cells(myrow, CFLifeExp).Value
looks at the same row as above but in column 8 which is defined in excel as a number. Sometimes it sets LifeExp to zero and other times, it sets it to the value in column 8 which is never a zero.
myrow, CFLifeExp and MyStr, CFRepDue are dimensioned as Integer
I can’t figure out why the macro works correctly sometimes but not always. Can you help. It’s always the same data. I am using Office 365 with latest updates on latest Windows 10 1803
Thank you for any help you can give
By: Terrance Cieslak