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.
--
Regards,
Tom Ogilvy
"Tony" wrote in message
...
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
"Tom Ogilvy" wrote in message
...
Sub AddApostrophe()
for each cell in Selection
if not cell.hasformula then
cell.Value = "'" & cell.Value
end if
Next
End sub
--
Regards,
Tom Ogilvy
"Tony Wainwright" wrote in message
...
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
|