Macro For Dividing a Named Range by 10

Hi Diane,

You may be able to do what you want manually in Excel:
a. Put 10 in an empty cell that is not in your 'TestData' range.
b. Copy that cell (either CTRL C or Edit > Copy).
c. Select the range you want to divide by 10 (Highlight the range with the mouse).
d. Edit > PasteSpecial. Select 'Values' and 'Divide' (both selected at the same time), then click on 'OK'.
e. Manual range division is complete.

I have a named range called TempData and the reference used is =OFFSET(TestData!$C$2,0,0,COUNT(TestData!$C:$C),12).

I had problems with 'COUNT(TestData!$C:$C)'. It always returned a value of 0 for me. I assumed you wanted all the rows in the range that are in Column C.

I would like to use a macro to divide each individual cell within this range by 10, but only the once. Then the macro will recognise the operation has been completed and will not calculate again.

There are two Macros that follow:
a. The first divides all non-zero cells in the entire 'TestData' range by 10.
b. The second divides all non-zero cells in the 'TestData' range by 10 starting at cell 'C2' for all rows in the range (row 2 thru n) and for exactly 12 columns.

In order to do this only once I had to borrow a cell (I arbitrarily selected 'A24') in your worksheet to act as a flag to indicate whether or not the division had taken place.

There are instructions in the Macros on what to modify if you want to:
a. Change the flag sentinel cell.
b. Hide the flag sentinel cell.
c. Change the number of columns or starting column.
d. Change the number of rows or starting row.

Lewis

Please Login or Register  to view this content.

Originally Posted by LJMetzger

I had problems with 'COUNT(TestData!$C:$C)'. It always returned a value of 0 for me.

Perhaps you only had text in column C? COUNT will only count numbers (COUNTA will count both).

Hi Izandol,

Thanks for taking a look at my problem.

It was my lack of formula knowledge. The User had a range named 'TestData' and I probably assumed wrongly that the 'TestData' in the formula:

Please Login or Register  to view this content.

referred to the 'Range'. It is probably a SHEET REFERENCE, and my Sheet has a different name. When I removed the 'TestData' from the formula, the COUNT function worked, as all the cells were numeric.

Lewis