Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How do I load this string without Excel aborting?

Posted on 2011-10-25
10
Medium Priority
?
283 Views
Last Modified: 2012-05-12
How do I load this string into an Excel Range without Excel aborting:

Range("A1").value = "===This is a test==="

aborts, whereas

Range("A1").value = "ABC===This is a test===" works.

I need to be able to load text strings into a cell that start with an equal sign.

Any help on this is greatly appreciated.
0
Comment
Question by:toddvoros
  • 6
  • 3
10 Comments
 
LVL 50

Accepted Solution

by:
Martin Liss earned 1336 total points
ID: 37026898
Range("A1").value = "'===This is a test==="
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 37026904
There's a single quote after the first double quote.
0
 
LVL 37

Assisted Solution

by:TommySzalapski
TommySzalapski earned 664 total points
ID: 37027091
If a cell starts with an equals sign, Excel thinks it contains a formula. Adding the single quote mark to the beginning tells it to treat it as text. The single quote will not print or display so it will look just like you want it to.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 50

Expert Comment

by:Martin Liss
ID: 37027101
TommySzalapski That's what my answer has:)
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 37027126
I know. I was referring to your answer, just adding the explanation and the reassurance that it's not going to mess up the look of the cell (a common concern).
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 37027132
Okay.
0
 

Author Comment

by:toddvoros
ID: 37027801
None of you have the correct solution.  
I thought of the solution while I was at lunch today and it works:

   
   
    Rem Force all cells in column to be treated as text. *
    Rem Prevents = being interpeted as a formula.        *
     
    Columns("A:A").NumberFormat = "@"
    Range("A1").value = "===This is a test==="
0
 
LVL 50

Assisted Solution

by:Martin Liss
Martin Liss earned 1336 total points
ID: 37028136
Your solution works of course but so should mine.
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 37028570
Yes, that works as well. The reason I think Martin's is better is that it works no matter how the cells are formatted. If you have ===Cash=== as the heading for a column and then you go to change the format of the whole column to currency, then the error comes right back. Martin's solution works no matter what; even if you copy and paste values into a different part of the sheet.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 37034526
Do you plan on accepting either TommySzalapski's or my answer?
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question