Results 1 to 3 of 3
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Finding a 'clean' column of cells (Excel 2000+)

    This posting is for information only. My code works and I am moving forward. Nonetheless, I know that someone, sometime, will read this and point out that my herculean efforts can be replaced by two lines of code, so here goes ...

    I d/l code from web sources (MSN Money Central, Google Finance, Yahoo etc.) and from time to time the data is incomplete. Typically the downloaded table will contain "#NA". My client says that that is the only nasty I'll see, but you and I know that tomorrow MS or GF or YF will glitch and send us an #ERR or perhaps cause a #VALUE, or whatever.

    The attached Excel2000 workbook contains:

    1) blnFaulty which will return TRUE (the range IS faulty) if any cell in the range contains a nominated string. Thus I can select a column of cells and learn if any one of those cells contains "#NA". If no string is specified, I learn only that one of the cells is NOT numeric. Cell M9 in the workbook calls rngCleanColumn without the optional parameter, so I am interested here in the first column that is "pure numeric"; any #NA, #ERR, #VALUE etc will cause that column to be rejected as a candidate.

    2) rngShiftRight is my crude method of obtaining a new range (non-faulty) from an original range. If the leftmost column of 10 cells contains a faulty value, I'd like to use the column to the right, unless it is faulty, in which case keep shifting to the right.

    3) rngClean returns a column that is not faulty, that is, it returns the leftmost column available that is "pure data", useable in our financial analysis work.

    4) rngCleanColumn tells me the column number of my leftmost column, so that I can employ the value in a VLookUp table.

    I do not consider Excel range manipulation to be my strong point. I generally spend so much time in the VBA help screens that the other part of my brain kicks in and suggests soemthing simple like VLookUp. This after about 8 hours struggling to overcome the limitations that "Functions do not modify the content of any cell including itself ."

    The supplied code is probably horribly inefficient in execution time, but I don't care, since we spend about five seconds per sheet interrogating the web. What's a few hundred milliseconds between friends, right?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding a 'clean' column of cells (Excel 2000+)

    The instruction in rngShiftRight can be simplified to

    Set rngShiftRight = rngin.Offset(0, 1)

    I don't see much reason for encapsulating this in a separate function, you might as well use

    Set rngResult = rngResult.Offset(0, 1)

    in rngClean. I think you should use a single-column range $B$9:$B$18 in rngCleanColumn in the formulas in column M instead of $B$9:$F$18. Because you shift the range in rngClean, it'll extend past the original range if you use the entire range. If there is a text value in column B (causing a shift), and also a text value in column G (just to the right of B9:F18), you'd get #REF.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding a 'clean' column of cells (Excel 2000+

    > The instruction in rngShiftRight can be simplified to
    Thank you. You are (were!) looking at the end result of my struggle to come to terms with the Range object. My struggle generally takes the form of tacking things on to the right-hand end until something works.

    > I don't see much reason for encapsulating this in a separate function
    You are correct. You will gather from my comments above that this function began life as a rather cumbersome affair, and has been whittled down to the bare bones, or so I thought until I read your improvement. I'll probably leave it as is, for the sake of readability.

    >in rngClean. I think you should use a single-column range $B$9:$B$18
    Again, correct. I had already trod on the toes of the #REF beast, but for the sake of getting something working was not too worried, as the real-life call is from a separate worksheet, so there is no danger of overlap. I will take another look, because I would not like the limitation "Should be called from a separate sheet" to be in force.

    I specified a 2-D range of cells to permit detection when passing the right-hand boundary, (which in our case means that there is not a single valid quarter for the past 18 months). For now, a #REF propogating upwards to the master sumamry will serve as a good alert, but in practice I'll probably return a special value to indicate that not a single valid column was detected within the given range.

    Our use is within a VLookup function, so the end-user is alreday thinking of a 2-D array of cells.

Posting Permissions

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