Will using vba macro LEN() fail if cell is empty?

[betboy25]

Hello,

I am looping through the rows of data for a column. I am trying to check to see if the cell has data. If it does, I format it to 0.00, else I do nothing. However, when the code encounters a cell that doesnt have data the execution of the code stops. No error or anything. Just stops. Here is the code I am using...

Please Login or Register  to view this content.

Is there anything wrong with this that anyone can tell? Any advice greatly appreciated.

Thanks

[TMS]

I'd like to see a sample workbook that exhibits the problem. How does the array get populated? What's in it in general, and what's in the element when it fails? As it's meant to be a column number, if it is zero the code will fail.


Regards, TMS

[betboy25]

Thanks for the reply! The array gets populated with the code below. I have checked the array and it does have the values I need in it.

Something I just noticed is that if I use actual numbers in the ".cells(4,4) part from the code in my first post, it seems to work but when I use variables it doesnt. I have done a msgbox right before the IF statement to check to make sure that the variables have the correct values and they do. Just for giggles I created two dummy variables and gave them a value of like 8 and 10 and tried using those in the expression and they too failed. When used in ANY statement such as IF, msgbox etc.... the page stops. I am new to vba macro coding so this is new to me... I also tried ISEMPTY() with the same results.

The code just places row numbers into the array.

Please Login or Register  to view this content.

PS: I can send you an example if you tell me how to do so using this website.

thanks!

[TMS]

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[betboy25]

Thanks for the information on uploading a file. I think I may have found out why the variables wont work the way they were set up. The array is a string array(containing numbers). When I put a cint(arrayName(xx)) around it, it worked. Guess it must be a variable of type integer to be used in that context. Wish it would have given me some indication of the problem instead of just stopping execution....

thank you for the information.

[TMS]

You're welcome. Looks like you did most of the work yourself


If you experience this sort of issue in the future, you can always try and check the cell address in the Immediate window. As yo step through the code, type:

?.Cells(xxx, arFTELine(xx)).Address

If one of the variables isn't valid, the print will probably complain ...


Regards, TMS