ADDING A "." AFTER THE FOURTH LETTER and RECEIVING AN ERROR #VALUE!

[Chris101]

Each month I receive a spreadsheet that has one circuit ID on it. The Circuit ID needs to be formatted the same way. When I receive the circuit ID's they need to be in the format - "BREC.123456..ATI" . The circuit IDs are now arriving as "BREC123456..ATI". There is no "." after the letter "C". I would like to place a "." after the fourth letter of every circuit ID in a column. In this case there is only one circuit ID.

when I run my code I receive the following error: #VALUE!

With Sheets("Load Tbl-ACU Broadband Cir").Range("S2:S" & Sheets("Load Tbl-ACU Broadband Cir").Cells(Rows.Count, "S").End(xlUp).Row)
        .Value = Evaluate("LEFT(" & .Address(, , , 1) & ",4)&""."" & RIGHT(" & .Address(, , , 1) & ",LEN(" & .Address(, , , 1) & ")-4)")
    End With

A member of the board helped me with the above code. when I run it in another workbook it works great. I copied it to my current workbook and it no longer works, and I get an error. The worksheets have the same names as I copied and pasted them from my original workbook to the one i am using now.

If anyone would be kind enough take a look at the code to see what I am doing wrong, I would appreciate it.

I have attached a picture of the worksheet i am using for review.

Thank you in advance for your time and help.

 

[Peter_SSs]

For the sample data in your image, what cell/s is/are the #VALUE! error appearing in.

BTW, please review #14 of the Forum Rules in relation to thread titles in all upper case.

 

[Chris101]

The error appears in cell "S2".

My apologies for using shouty caps. It won't happen again.

 

[Peter_SSs]

My apologies for using shouty caps. It won't happen again.

Cheers, thanks.

The error appears in cell "S2".

I have not been able to reproduce the error in that cell after manually entering that sample data in S1:S2

Clearly we do not have your full code, perhaps it might be worth posting that in case the error somehow relates to the rest of the code.

Have you tried closing right out of Excel altogether and opening up again & trying?

If still getting the problem can you give us the sample data with XL2BB?

 

[Chris101]

Cheers, thanks.


I have not been able to reproduce the error in that cell after manually entering that sample data in S1:S2

Clearly we do not have your full code, perhaps it might be worth posting that in case the error somehow relates to the rest of the code.

Have you tried closing right out of Excel altogether and opening up again & trying?

If still getting the problem can you give us the sample data with XL2BB?

Thank you for your time.

I did close out of excel and reopened it. I still get the same error.

Yes, of course I can give you the data with XL2BB. if you don't mind, I will provide the data tomorrow. I will download XL2BB in the morning so you can see what I have done.

I do appreciate your time and assistance.

 

[Peter_SSs]

Cheers.
BTW, I would simplify the Evaluate line to this

.Value = Evaluate("replace(" & .Address & ",4,0,""."")")

 

[Chris101]

Cheers.
BTW, I would simplify the Evaluate line to this

.Value = Evaluate("replace(" & .Address & ",4,0,""."")")

Peter_SSs:, It worked. When you said to close out of excel, it dawned on me that the worksheet is actually downloaded from AT&T and i used the AT&T worksheet for my code. There must be something with the AT&T worksheet that triggers the error. I say that because I took the data and code and moved it to a new workbook. and it worked great.

also, your simplification of the code worked great.

You have solved my problem. Thank you very much for your assistance, time and knowledge.

Best Regards,
Chris

 

[Peter_SSs]

You're welcome. Glad it has worked out now.