apostrophe problem

  • Thread starter Thread starter Belinda Robinson
  • Start date Start date
B

Belinda Robinson

Following on from an earlier query...
I have a spreadsheet (Excel 2000 SR1) that uses concatenation to create a
full filepath per row, each row representing a cell in a spreadsheet.

The concatenatation formula (in formula bar) is:
="="&A1&B1&C1&D1&E1&F1&G1&I1
This shows ='T:\PROMPT\8010-Astronomy\SDR000Z-PPARC\[SDR8200.xls]Monthly
Summary'!$O$13 in the cell.
When I copy the cell and paste special/values into the next cell I get this
in the formula bar
'='T:\PROMPT\8010-Astronomy\SDR000Z-PPARC\[SDR8200.xls]Monthly
Summary'!$O$13

I don't see the initial ' apostrophe in the actual cell.
If I delete the initial ' apostrophe from the formula bar I get the actual
value of cell O13 from the SDR8200.xls file - which is what I want.

Trouble is, I need the data from 450 spreadsheets so don't want to have to
go through each line manually fixing it. If I try edit/replace it doesn't
work because the '=' is not found (presumably as it's only showing in the
formula bar...)

Does anyone know how I can get round this? I haven't mentioned that I need
to pick up info from nine other cells on each spreadsheet too, so if there's
no other way, I'll have to manually alter 450 x 9 cells so I hope there is a
solution! :-)

Thanks in advance.
Belinda
 
try this

Sub FixRangeValues()
For Each C In Selection
C.Value = Format(C, "00")
Next
End Sub
 
Back
Top