FORMATING NUMBER FORMATS TO EXACT NUMBERS.

[sonto]

have a workbook with staff id numbers and all IDs are formated as '000000' in
that if you enter 132 in the range 400132 is dipayed so for me to convert it
into values I have to paste in the notepad and then repaste back in the same
range as values -paste special -values.) is there a way I can do the same
repetative procidure in a workbook of four sheets in ranges through VBA? I
TRIED =TEXT(A1"400") BUT THAT MEANS I HAVE TO PASTE BACK AS VALUES same cyle
of doing the same repetative job which i want to eliminate
(sheet1 rangeA1:A6000)-(F1:F60000)
(sheet2 range A1:A6000)-(F1:F60000)
(sheet3 range A1:A6000)-(F1:F60000)
(sheet4 range B1:B500)-(D1500)-(G1:G500)-(J1:J500)
Apreciates any help

 

[OssieMac]

Not sure I fully understand. You said that the range is formatted as "000000"
so that you enter 132 and it displays 400132. If this is the case then I
suggest that it is formatted as "400000" so the 132 displays as 400132 but
the real value is still only 132.

If my assumption is correct and I understand correctly that you want the
displayed number of 400132 to also be the value then you can insert a helper
column and insert the following formula. Assumes that initial value is in
cell A2 and formula is in B2.

=VALUE(TEXT(A2,"400000"))

The formula first of all creates a text string of 6 characters begining with
4 with whatever number value is in A2 and then converts that string to a
numeric value.

 

[sonto]

THANKS A MILLION!! for the array.it works perfect.
1,Now the thing is am trying to eliminate the daily-almost endless
copy/paste thing in new worksheets as I have to resend the workbook back and
I got to make sure that it goes back the way it came only with remarks added
to it.{minus the part of ID NO(s)}
2.The staff numbers (plus names,designation and everything else) are
formated in in diffrent formats -colour,size ets.how can i retain the
original colour and other formats in each id?
NB Am kind of new to VBA but think can handle some coding -- anything will
be a blessing.

 

[OssieMac]

I don't understand enough of what you are trying to achieve to provide any
code for you.

You say you are trying to eliminate the daily task of copy/paste etc and
have to return the workbook in it's original state without the changes you
make to the Id's. So why is it necessary to change the Id's? What are you
doing with them that makes it necessary to change their format?
Even if it is necessary you can insert the new Id's in a new temporary
column without removing the old column and when finished doing whatever you
have to do with the Id's in the new format then just delete the temporary
column and that leaves the original column of Id's in their original format.

In order to provide any code for you I would need to know exactly what it is
you are trying to achieve with the code.

If you are going to use code then it is probably best placed in another
workbook so that the workbook in question is left without macros unless it
already has macros in it. This eliminates problems created for other people
when opening the workbook if they have not got their security settings
properly set to allow the macros.

If you believe that you can handle some code then open and save a new
workbook to contain the code then try recording a macro of what it is you
want to do in the data workbook. The recorded macro is unlikely to be generic
enough to use on the workbook when the number of rows etc change but if you
post the code you record then maybe I can edit the code to ensure that it is
generic for any number of rows to handle etc.

NOTE: Ensure that you have a backup copy of the workbook before running any
code etc.