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

Option Explicit

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'The following item defines where the 'Sentinel Cell' is to go.
'When the cell is BLANK, then division is allowed.
'After division is performed, the Macro puts a value in the cell that prevents future division.
'
'You can make this cell hidden by using custom format of three semi-colons (;;;)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Const sDivisionAllowedSentinel_CELL = "A24"

Sub DivideContentsOfEntireNamedRangeByTenOnlyOnce()
  '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  'This divides the Entire Contents of range 'TestData' by 10
  'The sentinel cell defined in the beginning allows division to occur only once
  '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  

  Dim r As Variant    'r is each cell in the range
  Dim myRange As Range
  
  Dim sSentinelValue As String
  
  'All the calculations are to be done on the Active Sheet
  ActiveSheet.Select
  
  'Get the value from the Sentinel Cell (Blank means Ok to divide/ anything else means DO NOT DIVIDE)
  sSentinelValue = Trim(Range(sDivisionAllowedSentinel_CELL).Text)
  If Len(sSentinelValue) > 0 Then
    MsgBox "TERMINATING.  Division Not Allowed." & vbCrLf & _
           "Division already occurred according to the value in Cell '" & sDivisionAllowedSentinel_CELL & "'."
    Exit Sub
  End If
  
  'Assign the range object
  Set myRange = Range("TestData")
  
  
  'Loop thru the range
  'Divide all non-zero values by 10
  For Each r In myRange
    'Debug.Print r.Address(False, False)
    If r.Value <> 0 Then
      r.Value = r.Value / 10#
    End If
  Next r
  
  'Set the Sentinel Cell
  Range(sDivisionAllowedSentinel_CELL).Value = "Divided by 10 on " & Now()
  
  'Clear the object
  Set myRange = Nothing

End Sub


Sub DivideContentsOfPartOfNamedRangeByTenOnlyOnce()
  '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  'This divides Part of the Contents of range 'TestData' by 10
  'The sentinel cell defined in the beginning allows division to occur only once
  '
  'The goal is to replace the following Excel function:
  '=OFFSET(TestData!$C$2,0,0,COUNT(TestData!$C:$C),12).
  '
  'The division range starts at cell C2 (row 2, column 3).
  'To change the start location change the following two lines as required.
  Const iFirstColumnInCalculation = 3      'Column C
  Const iFirstRowinCalculation = 2         'Row 2
  '
  '
  'The division is supposed to be for 'COUNT(TestData!$C:$C)' rows.
  'This function always returned 0 for me.
  'This function uses all the rows in the range starting at the first row defined above.
  'To change the number of rows the calculation of the variable 'iRowCount' MUST BE MODIFIED.
  '
  'The division is supposed to be for 12 columns.
  'To change the NUMBER OF COLUMNS change the following line.
  Const iColumnCount = 12
  '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

  Dim r As Variant    'r is each cell in the range
  Dim myRange As Range
  
  Dim iRowCount As Integer
  Dim iFirstColumnInRange As Integer
  Dim iFirstRowInRange As Integer
  Dim iLastColumnInCalculation  As Integer
  Dim iLastColumnInRange As Integer
  Dim iLastRowInCalculation  As Integer
  Dim iLastRowInRange As Integer
  Dim iRowsInRange As Integer
  Dim iColumnsInRange As Integer
  
  
  Dim sSentinelValue As String
  
  'All the calculations are to be done on the Active Sheet
  ActiveSheet.Select
  
  'Get the value from the Sentinel Cell (Blank means Ok to divide/ anything else means DO NOT DIVIDE)
  sSentinelValue = Trim(Range(sDivisionAllowedSentinel_CELL).Text)
  If Len(sSentinelValue) > 0 Then
    MsgBox "TERMINATING.  Division Not Allowed." & vbCrLf & _
           "Division already occurred according to the value in Cell '" & sDivisionAllowedSentinel_CELL & "'."
    Exit Sub
  End If
  
  'Assign the range object
  'Find the starting row and column in the range
  'Find the ending row and column in the range
  Set myRange = Range("TestData")
  iFirstRowInRange = myRange.Row
  iFirstColumnInRange = myRange.Column
  
  iRowsInRange = myRange.Rows.Count
  iColumnsInRange = myRange.Columns.Count

  iLastRowInRange = iFirstRowInRange + iRowsInRange - 1
  iLastColumnInRange = iFirstColumnInRange + iColumnsInRange - 1
   
   
   
  
  
  '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  'Find the number of rows in the range
  'The number of columns is defined at the beginning of the function
  '
  'The number of rows calculated is determined by variable 'iRowCount'
  'The number of columns calculated is determined by variable 'iColumnCount'
  '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  iRowCount = iRowsInRange
    
    
    
    
    
    
  'Redefine the range to start at 'C2' (row 2, column 3)
  'and go on for the number of rows and columns
  'If the number of rows and columns is too many - can't go outside the range
  iLastRowInCalculation = iFirstRowinCalculation + iRowCount - 1
  iLastColumnInCalculation = iFirstColumnInCalculation + iColumnCount - 1
  
  If iLastRowInCalculation > iLastRowInRange Then
    iLastRowInCalculation = iLastRowInRange
  End If
  
  If iLastColumnInCalculation > iLastColumnInRange Then
    iLastColumnInCalculation = iLastColumnInRange
  End If
  
  
  'Set the redefined range
  Set myRange = _
    Range(Cells(iFirstRowinCalculation, iFirstColumnInCalculation), _
    Cells(iLastRowInCalculation, iLastColumnInCalculation))
  
  'Loop thru the range
  'Divide all non-zero values by 10
  For Each r In myRange
    'Debug.Print r.Address(False, False)
    If r.Value <> 0 Then
      r.Value = r.Value / 10#
    End If
  Next r
  
  'Set the Sentinel Cell
  Range(sDivisionAllowedSentinel_CELL).Value = "Divided by 10 on " & Now()
  
  'Clear the object
  Set myRange = Nothing

End Sub

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:

COUNT(TestData!$C:$C)'.

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