Independent developer

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
                Else
                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

Leave a Reply

Your email address will not be published. Required fields are marked *