Add apostrophe

  • Thread starter Thread starter Tony Wainwright
  • Start date Start date
T

Tony Wainwright

Hi guys

I am writing an Access d/b that uses an Excel spreadsheet as it's data
source. The spreadsheet contains about 24,000 rows. I have noticed that in
some of the columns numbers are formatted with an apostrophe. I would like
to create a routine that searches through specified columns and adds an
apostrophe to the start of each entry, but I am not that familiar with
Excel. Does anyone know where I might find such a routine
 
Sub AddApostrophe()
for each cell in Selection
if not cell.hasformula then
cell.Value = "'" & cell.Value
end if
Next
End sub
 
Thanks Tom,

Tat works fine. Changed cell to ActiveCell, except in For Each ... Next
Loop which generates an error. How do you loop through a range?

Tony
 
Code works as posted if you have a range selected and you don't have option
explicit declared. If only one cell is selected, then it only operates on
the activeCell.


Sub AddApostrophe()
Dim cell as Range
for each cell in Selection
if not cell.hasformula then
if not isempty(cell) then
cell.Value = "'" & cell.Value
End if
end if
Next
End sub

Put in an added check so it doesn't put an apostrophe in an empty cell.

Sub AddApostrophe()
Dim cell as Range
for each cell in Selection
if not cell.hasformula then
if not isempty(cell) then
if isnumeric(cell) then
cell.Value = "'" & cell.Value
end if
End if
end if
Next
End sub

Only operates on numeric cells in the selection.

So this is looping through a range.
 
Back
Top