How do I create a function that divides range of excel cells by 1000?

[larissalle]

I have a range of cells that are in $##,###.00 format. I need the cells to be in thousandths format, i.e., instead of $16,400.00, it should be $0.16 instead. I tried naming the range and creating a loop that loops through the range and divides each cell by 1000, but it doesn't work due to a type mismatch on line 2. Says that Item should be a variant or object. I'm pretty good at debugging, but writing code is still new to me.

Code:
name the range in the excel file as "Range", then:

1 Dim Item as Double
2 For Each Item in Worksheet.Cells("Range&quot
3 Item = Item/1000
4 Next Item

Help!!
Larissalle

 

[DaleWatson123321]

Hi Larisalle,

First, just to confirm... 16,400 divided by 1000 = 16.4 (not .16)

Here's a possible "easy" method...

Steps:

1) In the column next to your range (you could insert a column temporarily if necessary), use a simple formula like =C1/1000 (where you replace C1 with the first cell in your range.

2) Copy this formula down for each row of your range.

If you happen to have a range-of-numbers, you could go to the first blank column to the right, and then enter the same type of formula, that would reference the cell in the top-left-corner of your range. Then copy the formula down-and-across for the number of columns in your range.

3) For the range containing your formulas, highlight the range.

4) Copy the Range, using <Control> C in one way.

5) Paste the range onto itself, using &quot;Paste Special&quot; and choosing &quot;Values&quot;. From the menu: Edit - Paste Special - Values.

6) Then copy your values to replace your previous values.

I hope you find this option suitable.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[acron]

Your code dimensions Item as data type Double, when in fact it should be an Object type Range.

The following code should do what you want

    Dim Item As [b]Range[/b]
    For Each Item In Range("Range")
         Item = Item / 1000
    Next Item

However, you do not need code at all. Enter 1000 in any unused cell (preferably formatted similar to the range you want to work with), copy that cell, select the range you want to divide by 1000,select Paste Special, and then select the operation Divide.

That should do the job.

If you are only concerned about how the data is displayed, you could apply the following custom format (including the trailing comma) :

$ #,##0,

A.C.

 

[acron]

Sorry, got mixed up with tags ;

The top line of code should read

Dim Item As Range


A.C.

 

[BrainB]

FWIW I think you are going down a dodgy road by changing your numbers. I have done this in the past and got into difficulties when linking to calculations with other data.

Why not keep your numbers the same and reformat the cells to show them the way you want with something like :
#,##0.00,;[Red](#,##0.00,); Regards
BrianB
** Let us know if you get something that works !
================================