Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    formula returns '' (97+)

    I have a date formula starting in column A1 and ending in A31--the formula outputs day of month. For months with less than 31 days the formula outputs to "". When I use
    "lrows = Worksheets("Sheet1").Range("a65536").End(xlUp).Row " to get the last row containing data for proccesing if "" exist they are taken into account as regular data and therefore the lrows value is "incorrect". I have gotten around this by extra coding and looking for "" and subtracting from "lrows" but I am sure there is a more elegant way.
    BTW I don't want to lose the formula in cells containing "" if user saves file. ---ie if same saved file is opened in new month correct number of days shall appear in column "A".
    TIA.
    Smbs

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: formula returns '' (97+)

    If the NumRows (to distinguish between the lastrow) is important to processing, why not calc this more directly by either COUNTing the values in col A (I assume there are no blanks within Col A data, just at the end.

    In your processing code, you could also keep your lrow designation and instead of accounting for blank rows in the lrow value, just use an IF to process ONLY when cell.value, range(x,1).value, however you do it does not equal a null.

    Steve

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: formula returns '' (97+)

    Yuu could try something like

    lrows = [A1].Offset(DateAdd("M", 1, [A1]) - [A1] - 1, 0).Row

    Andrew C

  4. #4
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula returns '' (97+)

    Thanx for quick responses--will try them out
    Smbs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •