How can I clear the values entered by the user without affecting my formulas?
	I'm having two problems with Application.Run method.  What can I do about this?
	How can I convert values from old systems into something that Excel will recognize properly
	How do I truncate values without rounding them?
	In a worksheet cell, can I restrict the input values without using combo boxes?
	From a large column of numbers, how do I collect the averages of every three numbers into adjacent cells?
	Why do I get an overflow error for: nSecs = nSecs + (10 * 60 * 60) even if nSecs is defined as double?

I have a data entry worksheet in my application that I need to erase between uses. However, it has many formulas and I don't want to clear these. How can I clear the values entered by the user without affecting my formulas?
Keywords: Formulas, Clearing
Posted November 26, 1996

This is a job tailor-made for the SpecialCells method of the Range object. The SpecialCells method allows you to operate on only certain types of cells within a range. Here is how you would use it to clear only constant values on a worksheet without affecting the formulas:

Worksheets("MySheet").UsedRange.SpecialCells(xlConstants, 23).ClearContents

I'm having two problems with Application.Run method. If a file name begins with less than three alpha characters (AA1234.XLS) Application.Run does not work and if a long file name has spaces within the name (MY TABLE.XLS) Application.Run does not work. What can I do about this?
Keywords: Application.Run
Posted November 26, 1996

You can solve both of these problems by surrounding the filename in single quotes. For example:

Application.Run "'AA1234.XLS'!MyProc"
Application.Run "'MY TABLE.XLS'!MyProc"

I frequently must import text files produced from our old mainframe computer system. The numbers in these file have + or - appended to the right of them to indicate their sign, so Excel doesn't recognize that they are numbers. How can I convert these into something that Excel will recognize properly?
Posted November 11, 1996
Keywords: Import Text Files

The following macro will convert old-style signed numbers into a numeric format that Excel will recognize.

Sub ConvertBackwardsSigns()

    Dim oEntry As Range
    Dim sTemp As String

    For Each oEntry In Selection

        sTemp = Trim(oEntry.Value)

        If oEntry.Value <> "" Then
            If Right(sTemp, 1) = "-" Then
                oEntry.Value = Left(sTemp, Len(sTemp) - 1) * -1
            ElseIf Right(sTemp, 1) = "+" Then
                oEntry.Value = Left(sTemp, Len(sTemp) - 1)
            Else
                oEntry.Value = sTemp
            End If
        End If

    Next oEntry

End Sub
	

How do I truncate values without rounding them?
Keywords: Truncate Formula Round
Posted September 11, 1996

The ROUNDDOWN() worksheet function does this for us. It is similar to the ROUND() function in behavior, except that it always rounds a number down, toward zero. So you have

   =ROUNDDOWN(17.99, 1)   gives   17.9
   =ROUNDDOWN(17.91, 1)   gives   17.9
   =ROUNDDOWN(17.91, 0)   gives   17
   =ROUNDDOWN(17.91, -1)  gives   10
   =ROUNDDOWN(-17.91, 0)  gives   -17
			

In a worksheet cell, can I restrict the input values without using combo boxes?
Keywords: Input Values Dropdowns Auto_Open
Posted July 6,1996

Dropdowns can be used to good effect on worksheets in order to minimize the chance of erroneous input. A similar effect can be obtained using the VBA code given below. Write a procedure similar to the one shown here to handle your input validation and assign the routine to the data entry event in your Auto_Open.

  ''' Limits input to a specified list of values
Sub LimitInput()
Dim vValidValues As Variant  ''' Holds array of values
Dim iValue As Integer        ''' Loop variable
Dim szInput As String        ''' Input cell value
      ''' Build the list of valid values
   vValidValues = Array("New York", "London", "Bombay", "Tokyo")
   If Not IsEmpty(ActiveCell) Then
         ''' Discard leading and trailing spaces
      szInput = Trim$(ActiveCell.Value)
      For iValue = LBound(vValidValues) To UBound(vValidValues)
            ''' Do case-insensitive comparison
         If StrComp(szInput, vValidValues(iValue), 1) = 0 Then
               ''' If input is valid, capitalize it as required
            ActiveCell.Value = vValidValues(iValue)
            Exit Sub
         End If
      Next iValue
   End If
   MsgBox "'" & szInput & "' is not a valid input."
   ActiveCell.Value = ""
End Sub
Sub Auto_Open()
   Application.OnEntry = "LimitInput"
End Sub
			

From a large column of numbers, how do I collect the averages of every three numbers into adjacent cells?
Keywords: Averages Column Range
Posted July 6, 1996

You could set up a fairly simple formula as follows. Let us say your list of numbers is in a range named MyValues. First, create two named cells (just above the list of numbers would be convenient): a cell named FirstRow with the formula  

  =ROW(MyValues)			

a cell named Chunk where you enter the value 3 In the first cell to the right of the cells containing the values, enter the formula

  =AVERAGE(INDEX(MyValues,Chunk*(ROW()-FirstRow)+1):INDEX(MyValues,Chunk*(ROW()-FirstRow)+Chunk))
			

and copy it down to the rest of the cells. That's it. If you change your mind later and desire to gather averages of five numbers at a time, just change the value in the single cell called Chunk to 5.

I am trying to calculate the number of seconds in ten hours. It works when the hours are a fraction, like 10.5 hours. Why do I get an overflow error for: nSecs = nSecs + (10 * 60 * 60) even if nSecs is defined as double?
Keywords: Calculate Fraction Seconds
Posted July 6, 1996

Excel tries to guess (intelligently in most cases!) the type of data to use, based on the individual operands. If one of the numbers was a decimal fraction, then the multiplication is assumed to be of double type numbers which can handle large values. In the given case, all numbers are integers but their product yields a number (36,000) larger than the valid range of values for integers (-32,768 to 32,767). To force Excel to use a larger data type, you could do one of the following:

a) Use a type suffix to one of the numbers to make Excel use the double data type which can handle the result:

  n = n + (10# * 60 * 60)
	

b) Make an explicit call to one of the type conversion functions:

  n = n + CLng(10) * 60 * 60
			

Back to Top

Back to the FAQ Table of Contents

The name is Baud......, James Baud.