number 2

Posted by ken on August 09, 2000 1:48 AM

how about this if there is an integer in the range("c9:c17") I would like it to be replaced by a fration of that number over the number in cell("v6")

so if c10 = 5 and v6 = 10 i would like to set c10 ="5/10" no reducing.

And do it automatically everytime the cells are changed

Posted by Ivan Moala on August 09, 0100 4:42 AM

Ken
Try this;

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TheRg As Range
Dim TheRgCells
Dim IsInteger As Boolean
Dim InRg
Dim Denom As Integer

Denom = Range("V6").Value 'Set denominator here

Set TheRg = Range("C9:C17")
Set InRg = Application.Intersect(Target, TheRg)
If Not (InRg Is Nothing) Then
For Each TheRgCells In TheRg
'Check if Integer
IsInteger = IIf(TheRgCells &LT;&GT; 0, (Int(TheRgCells) - TheRgCells) = 0, False)
If IsInteger Then
'If TheRgCells = Denom the handle view here
If TheRgCells = Denom Then
TheRgCells.NumberFormat = "?" & "/" & Denom
Else
TheRgCells.NumberFormat = TheRgCells & "/" & Denom
End If
'Stop recursion due to change
Application.EnableEvents = False
TheRgCells.Formula = "=" & TheRgCells & "/" & Denom 'Range("V6")
Else
If Not (TheRgCells.HasFormula) Then TheRgCells.NumberFormat = "General"
End If
Application.EnableEvents = True
Next
End If
End Sub

Ivan