+ Reply to Thread
Results 1 to 5 of 5

How to change the cell reference in a LEN formula by using Active cells

  1. #1
    Registered User
    Join Date
    02-22-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    25

    How to change the cell reference in a LEN formula by using Active cells

    Good day to everyone. I'm relatively new to Macro excel and I am having problems regarding counting the length of characters in my program.

    I have a program that requires 110 fields that has different character limits. For example: Field1 must limit the number of characters by 3, Field 2 must be limited to 10 characters etc. until field 110.

    Now I used the LEN argument with this code:

    Please Login or Register  to view this content.
    I inputted these formula in rows A3 to DF3 (110 fields) so that It will return a TRUE/FALSE statement per cell.

    Now my problem is, I have a lot of rows that must be checked and they are all displayed in rows A5 to DF5, A9 to DF9, A16 to DF16 etc. etc.

    I need to check all of their character limits per set of row, but there's just too many. Editing will take too much time so I was thinking, Is it possible to use the LEN argument by referencing the active cell?

    I was thinking that if I select a particular cell, then my formula's cell reference will change based on the cell I selected.

    Like for example, instead of just referencing a particular cell with the =LEN(A5)<=3, can I do something like this: =LEN(ActiveCell.Select)<=3?

    Obviously, this formula of =LEN(ActiveCell.Select)<=3 doesn't work, I just used it to describe what I wanted to do. I tried looking at the forum but I couldn't find anything that was like my problem.

    I'm sorry if it was long I wanted to be descriptive about it xD Thank you
    Last edited by crisshinn; 03-05-2011 at 01:19 AM.

  2. #2
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: How to change the cell reference in a LEN formula by using Active cells

    Is there something that could indicate a row should be checked or a pattern to the rows that need to be checked? If so you should be able to perform a loop to run down the sheet checking the required rows.

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.

  3. #3
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: How to change the cell reference in a LEN formula by using Active cells

    Have you also tried data validation?

    Dom

  4. #4
    Registered User
    Join Date
    02-22-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: How to change the cell reference in a LEN formula by using Active cells

    Actually, my program is a file uploader that checks the uploaded file's format.

    The file I upload is a text file that contains numerous transactions per line. The rows I need to check in that text file is just the header which is displayed in rows A5 to DF5 which are the 110 fields. A transaction is defined by its Header, which is the One I need to check and validate.

    The problem is, a text file may contain numerous transactions, thus, numerous Headers that all needs to be checked. Not to mention, the place where the headers are located are not predefined, such as Header 1 may be displayed in rows A7 to DF7, Header 2 can be displayed in rows A25 to DF25 etc. The length is really dependent on the number of lines per transaction, thus it's really hard to pinpoint where the next header may be located as all transactions are printed continuously in the worksheet.

    If it's just 1 transaction, then I can easily check the lengths because I made the first header to be Always displayed in rows A5 to DF5. which is where I referenced my formula. But since each transaction has no predefined length, the next batch of headers can be displayed in any row depending on where the previous transaction ended.

    Thats why I thought that maybe I could check the character limit of a particular cell if my formula has no particular cell reference.

  5. #5
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: How to change the cell reference in a LEN formula by using Active cells

    Are you able to upload a sample of the file? Might then be able to see some way round this.

    Dom

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1