VBA to put apostrophe if cell not empty

[craigg3]

I have a range of cells (E7:J50) that I need to put a leading apostrophy to any of the cells that are not empty. After my macro runs, I also need to remove the leading apostrophy from non empty cells (E7:J50)

 

[BenGee]

Hi

This will put an apostrophy to all cells that're not empty;

Dim c As Range

For Each c In Range("E71:J50")
If c.Value <> "" Then
c.Value = "'" & c.Value
End If
Next

Just so I'm clear on the second point, the above code won't input data into any blank cells. Do you still need to remove the apostrophy for these other cells? If so, does the cell only have an apostrophy in it or is there some other contents too?

 

[mumps]

Can you explain why you need the apostrophe before the macro runs and not after it runs? Can you please post the macro you are using?

 

[craigg3]

Hi

This will put an apostrophy to all cells that're not empty;

Dim c As Range

For Each c In Range("E71:J50")
If c.Value <> "" Then
c.Value = "'" & c.Value
End If
Next

Just so I'm clear on the second point, the above code won't input data into any blank cells. Do you still need to remove the apostrophy for these other cells? If so, does the cell only have an apostrophy in it or is there some other contents too?

It would just need to remove the leading apostrophy from any non-empty cells. The non-blank cells will have more than just an apostrophy in it.

 

[Fluff]

How about

Sub craigg()
   With Range("E7:J50")
      .Value = Evaluate(Replace("if(@="""","""",""'""&@)", "@", .Address))
   End With
End Sub