add an apostrophe at the beginning of the cell

[ramyadixit99]

Hi,

I have a sheet which has =========== in some of the cells.
Macro i need to write should do the following
Check each cell in the sheet
If it begins with =, it needs to add an ' (apostrophe) at the beginning.

So that excel doesnt treat the = as a formula.

Regards,
Ramya

 

[wrightyrx7]

Amended my code using Zaska's idea of making the code run on all the cells you have highlighted.

Just incase Zaska's code doesnt work because of the

cell.HasFormula

Sub ap()
Dim Rng As Range, aCells As Range
With ActiveSheet
    Set Rng = Selection
        For Each aCells In Rng
            If Left(aCells, 1) = "=" Then aCells.Value = "'" & aCells.Value
        Next aCells
End With
End Sub

 

[Rick Rothstein]

Here is another macro for you to consider (it should be faster than what has already been proposed)...

Sub PutApostropheInFrontOfEqualSigns()
  Dim R As Long, C As Long, LastUsedRow As Long, LastUsedCol, Data As Variant
  LastUsedRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  LastUsedCol = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
  Data = Range("A1", Cells(LastUsedRow, LastUsedCol))
  For R = 1 To UBound(Data)
    For C = 1 To UBound(Data, 2)
      If Data(R, C) Like "=*" Then Data(R, C) = "'" & Data(R, C)
    Next
  Next
  Range("A1", Cells(LastUsedRow, LastUsedCol)) = Data
End Sub

 

[wrightyrx7]

Haha you following me Rick, making all my code look rubbish!

 

[zaska]

Here is another macro for you to consider (it should be faster than what has already been proposed)...

Sub PutApostropheInFrontOfEqualSigns()
  Dim R As Long, C As Long, LastUsedRow As Long, LastUsedCol, Data As Variant
  LastUsedRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  LastUsedCol = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
  Data = Range("A1", Cells(LastUsedRow, LastUsedCol))
  For R = 1 To UBound(Data)
    For C = 1 To UBound(Data, 2)
      If Data(R, C) Like "=*" Then Data(R, C) = "'" & Data(R, C)
    Next
  Next
  Range("A1", Cells(LastUsedRow, LastUsedCol)) = Data
End Sub

Thank you very much , it will take me some time to understand your solution

 

[AkaTrouble]

just as a thought out loud if the OP has ====== as the string would it not be easier or quicker to just check if 2nd character is "=" and add the apostrophe

thi would not change any formula's as their 2nd character would be a space

 

[Rick Rothstein]

this would not change any formula's as their 2nd character would be a space

It is my understanding that there are no formulas... that all the data was imported into Excel as text.

 

[AkaTrouble]

It is my understanding that there are no formulas... that all the data was imported into Excel as text.

yeah reading the threads it would appear that the data to be changed was imported

it was the first post that stated "some fields" and the further discussion after about formula's that prompted my comment

either way been following the thread as the alternative ideas for solutions have been very interesting

wrightyrx7 has a straight forward approach but Rick Rothstein has pure "black magic " as in advanced considered approach taking into account fundamentals of efficiency and speed

i have learned lots from both just by following your replies to others, so if my comment in anyway appeared to be questioning previous solutions then i apologise as said i was thinking out loud

 

[wrightyrx7]

Rick Rothstein has pure "black magic "

Agreed!