Remove "=" from cell with VBA in Excel

[end922]

Greetings,

I received a file that has a column formatted as general. I first format the column as text. The contents of the cells in this column contain alph numeric and symbols. I need in my code to be able to scan through each row and if a cell in that column begins with = then simulate F2 and enter so the #NAME? is removed and the cell value is visible.
here is what I have. So far it is not reliable.
Any suggestions.

  col_FacID = FindColumn(str_FacID, .Range("A1:Z1"))
            Dim Variant_FacID As Variant
            Columns(col_FacID).Select
            Selection.NumberFormat = "@"
             TotalNoOfRows = .Cells(65536, col_FacID).End(xlUp).Row
             .Application.DisplayAlerts = False
             For i = TotalNoOfRows + 1 To 1 Step -1
                .Cells(i, col_FacID).Select
                AppActivate DataSource1Name, False
                SendKeys ("{F2}'^+{ENTER}"), False
                 .Cells(i, col_FacID).Select
             Next
             .Application.DisplayAlerts = True

 

[SkipVought]

Hi,

#NAME is not really there. It is just a DISPLAY, telling you something very important. See Excel HELP.

You have a formula in the cell. When you change the cell format to TEXT, the value of that formula will never display. All you will see displayed is the formula.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[end922]

Hello, agree there is error in the field but not realy. The field is an ID field and just about any character or symbol is a valid id for that record. These cells do not contain formulas. When I change the field or column to text the field displays #Name?. Now I need to edit the field using VBA as if I was using the F2 key after the field/column has been changed to text.

 

[SkipVought]

If this field does not contain a formula, then were did the "=" come from?

You're not telling the whole story, I'm afraid, so, consequently, I cannot help you.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

And even if you were, why VBA?

Did you try Edit > Replace...?????

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[end922]

Skip,

edit replace wont due.

The whole story. The file is received from our loans department. It is a daily record of all loan transactions processed the previous day from all over the world. There are literally thousands of them. The id field as I believe I mentioned in my first and second post it says that field contains alph numeric and symbols and "=" being on of them. In some cases the field begins with "=" and in some cases it can be anywhere in the cell. Just by opening the file it has an error in that field because of the "=" being in front. Now that I have in VBA already done tons of dissecting and manipulating because I am trying to save head count by replacing a person from doing this daunting task I simply need to roll through that column and if the cell begins with a = edit the cell as if you pressed f2 on your keyboard so they can physically see the value of that cell and not #NAME.

 

[SkipVought]

edit replace wont due.

Please explain WHY Edit > Replace won't do? It seems simple enough to me.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[end922]

Skip. edit replace in the column will not work as the = can be anywhere in the cell. Edit replace I can't get to work in a single cell as the error prevents anything from happening. I've tried many scenarios but the only one can find to work is simulating F2 and enter after I change the field to a text. It seems simple but is quite disturbing.

 

[kiwidancer]

try putting this (or some version of it) into your loop

Dim str as String
str = Cells(i, col_FacID).Formula
if left(str,1) = "=" then
Cells(i, col_FacID) = "=""" & str & """"
end if

 

[csmithwick]

Change your code to the below: (adding a ' character at the beginning of a cell's content tells Excel to treat everything after it as text - the ' won't display)

col_FacID = FindColumn(str_FacID, .Range("A1:Z1"))
Dim Variant_FacID As Variant
Columns(col_FacID).Select
Selection.NumberFormat = "@"
TotalNoOfRows = .Cells(65536, col_FacID).End(xlUp).Row
.Application.DisplayAlerts = False
For i = TotalNoOfRows + 1 To 1 Step -1
.Cells(i, col_FacID).FormulaR1C1 = "'" & .Cells(i, col_FacID).FormulaR1C1
Next
.Application.DisplayAlerts = True

 

[DougAJ4]

The line Cell.value = cell.formula should do what you want:

Sub ConvertEq()
Dim textrange As Range, cell As Range
Set textrange = Range("myrange")
For Each cell In textrange.Cells
cell.Value = cell.Formula
Next cell
End Sub

Doug Jenkins

http://newtonexcelbach.wordpress.com/

 

[end922]

Thanks Everyone appreciate your help. kiwidancer,cmithwick,and DougAJ4. I ended up using this
Thank you for your help

               TotalNoOfRows = .Cells(65536, col_FacID).End(xlUp).Row
               Set TextRange = .Range("D2:D" & TotalNoOfRows)
               For Each TextCell In TextRange.Cells
               If Left(TextCell.Formula, 1) = "=" Then
                    TextCell.Value = "'" & TextCell.Formula
                Else
                    TextCell.Value = TextCell.Formula
                End If
                
               Next TextCell

Then on to

             TotalNoOfRows = .Cells(65536, col_FacID).End(xlUp).Row
             For i = TotalNoOfRows To 2 Step -1
                FacTextName = .Cells(i, col_FacID)
                'MsgBox FacTextName
                If Left(FacTextName, 1) = "=" And Mid(FacTextName, 2, 1) = "+" Then
                    .Cells(i, col_FacID) = Mid(FacTextName, 2, Len(FacTextName))
                End If
                If Left(FacTextName, 1) = "=" And Mid(FacTextName, 2, 1) = "-" Then
                    .Cells(i, col_FacID) = Mid(FacTextName, 2, Len(FacTextName))
                End If
              Next