' Assign a 0 (zero) as a false return flag value. ' Fail safe exit when no matching value is found. ' Loop until found, but exit when not found.ĭo Until lookupRange.Cells(lookupIndex, 1).Value = lookupWord Loop 'Return a 1 when the value is found and zero when it isn't.įunction Finding(lookupWord As String, lookupRange As Range, lookupColumn As Integer) As Integer If lookupIndex = (lookupMax + 1) Then ' Assign a 0 (zero) as a false return flag value.Įxit Do End If ' Increment counting index value. ' Print diagnostic value to determine what's evaluated. Do Until lookupRange.Cells(lookupIndex, 1).Value = lookupWord ' Loop until found, but exit when not found. ' Print starting and ending index values. Dim lookupIndex As Integer Dim lookupMax As Integer Dim returnValue As Integer ' Assign values to variables. The more complete solution requires using the TRUNC function around the source date ( B4 or C4 in the following example) to ensure the IF statement compares dates not time-stamps.įunction Finding(lookupWord As String, lookupRange As Range, lookupColumn As Integer) As Integer ' Define variables. That’s why it returned the first of the next month instead of the first of the current month. against the result of the EOMONTH function (11/1/13 at 12:00 A.M.). It wasn’t robust enough to support a comparison of 11/1/13 at 12:01 A.M. The IF function I provided was designed to compare an integer-based date cell value against the result of the EOMONTH function. The EOMONTH function always returns an integer. That’s because Excel only supports a NUMBER data type, which can be an integer or real number. Unfortunately, the EOMONTH function is a pseudo-overloaded function, and the EOMONTH function takes an int or double (integer or real number) as it’s parameter. of any day, and real numbers can be any time of the day. Integer values in Excel are numeric values for 12:00:00 A.M. That meant the source value in the cell wasn’t an integer. The formula returned the first day of the next month when it should have returned the first day of the current month. The user ran into a problem with the example because the date was being calculated and not an integer value.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |