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

**crisshinn** · 02-22-2011, 09:54 AM

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

**Domski** · 02-22-2011, 10:07 AM

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

**Domski** · 02-22-2011, 10:11 AM

Have you also tried data validation?

Dom

**crisshinn** · 02-22-2011, 10:31 AM

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.