Format and Convert

  • Thread starter Thread starter Edgar
  • Start date Start date
E

Edgar

Hi

Frank Kabel kindly helped me earlier with the following
code to save the workbook with a date stamp - it worked
the first time i ran this but keeps failing now with the
following error:

Compile error: Wrong number of arguments or invalid
property assignment!!

I have tried renaming the book before I run the macro but
this doesnt seem to do anything.

Also column L contains the data like the following:
1.6M
0.02M
2.5M

Where M stands for Million, i would like to convert this
to an actual figure - does anyone have any ideas?

TIA
 
Edgar,

Is the error in the SaveAs line (it looks fine to me) or somewhere else?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi

Sorry but the it looks like it was where the saveas
command was placed in my code that was causing the problem.

Any ideas on
Also column L contains the data like the following:
1.6M
0.02M
2.5M

Where M stands for Million, i would like to convert this
to an actual figure - does anyone have any ideas?

TIA
 
Hi Edgar
just paste the code as it is in your workbook right now and post it in
this NG. Also idicate the exact line which causes the error

For your other question you may use the following formula:
=1000000*LEFT(A1,LEN(A1)-1)
and copy this down
 
Hi Frank

Sorry didnt make it clear - I fixed my problem re: saving.

Is it possible to convert the contents of the column as
described below in vba instead of a formula as it is part
of a big process?

Thanks so much
 
Hi
try the following: Select your cells and invoke the following macro

sub change_values()
Dim rng as range
Dim cell as range

set rng = selection
For each cell in rng
If cell.value<>"" then
cell.value = cdbl(left(cell.value,len(cell.value)-1))*1000000
end if
next
end sub
 
Edgar,

For i = 1 To Cells(Rows.Count,"A").End(xlUp).Row
cells(i,"A").value =
left(cells(i,"A").Value,len(cells(i,"A").Value)-1)*1000000
Next i

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top