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

--
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

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

Try using Tom's code but add 'Dim Cell as Object' before the loop.

--
Regards
Alexander Artamonov

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