Function to display a cell containing an underlined number
I have a cell with some formatting (an underlined digit) on one worksheet and
I would like to display it on another worksheet. Why won't the following code display the cell. The line inside the "IF" complains that "A value used in the formula is of the wrong type". I've tried many variations of this but this one I think gets the point across. Function CopyNumberWithOneUnderlinedCharacter(ByVal myCell As Range) Dim l_Position As Integer For l_Position = 1 To Len(myCell.Value) If myCell.Characters(Start:=l_Position, Length:=1).Font.Underline _ < xlUnderlineStyleNone Then CopyNumberWithOneUnderlinedCharacter.Characters(St art:=l_Position, Length:=1).Font.Underline = xlUnderlineStyleSingle End If Next l_Position End Function |
Function to display a cell containing an underlined number
Hi,
does it have to be a function? A function usually returns a value, it does not manipulate cells (i.e. adding format etc). I suggest the following procedure instead, for which you can create a button that will execute it. Sub copy() Dim source As Range, dest As Range Dim sourceName As String, destName As String sourceName = InputBox("Source cell: ") destName = InputBox("Destination cell: ") Set source = Range(sourceName) Set dest = Range(destName) dest = source dest.Font.Underline = xlUnderlineStyleSingle dest.NumberFormat = "0" End Sub Regards, Alex "jquiet" wrote: I have a cell with some formatting (an underlined digit) on one worksheet and I would like to display it on another worksheet. Why won't the following code display the cell. The line inside the "IF" complains that "A value used in the formula is of the wrong type". I've tried many variations of this but this one I think gets the point across. Function CopyNumberWithOneUnderlinedCharacter(ByVal myCell As Range) Dim l_Position As Integer For l_Position = 1 To Len(myCell.Value) If myCell.Characters(Start:=l_Position, Length:=1).Font.Underline _ < xlUnderlineStyleNone Then CopyNumberWithOneUnderlinedCharacter.Characters(St art:=l_Position, Length:=1).Font.Underline = xlUnderlineStyleSingle End If Next l_Position End Function |
Function to display a cell containing an underlined number
Hi again,
Although the code I posted works, use the following, which is more correct from an object-oriented perspective, just in case (I have changed the "dest = source" for "dest.Value = source.Value"): Sub copy() Dim source As Range, dest As Range Dim sourceName As String, destName As String sourceName = InputBox("Source cell: ") destName = InputBox("Destination cell: ") Set source = Range(sourceName) Set dest = Range(destName) dest.Value = source.Value dest.Font.Underline = xlUnderlineStyleSingle dest.NumberFormat = "0" End Sub Regards, Alex "Alex Guardiet" wrote: Hi, does it have to be a function? A function usually returns a value, it does not manipulate cells (i.e. adding format etc). I suggest the following procedure instead, for which you can create a button that will execute it. Sub copy() Dim source As Range, dest As Range Dim sourceName As String, destName As String sourceName = InputBox("Source cell: ") destName = InputBox("Destination cell: ") Set source = Range(sourceName) Set dest = Range(destName) dest = source dest.Font.Underline = xlUnderlineStyleSingle dest.NumberFormat = "0" End Sub Regards, Alex "jquiet" wrote: I have a cell with some formatting (an underlined digit) on one worksheet and I would like to display it on another worksheet. Why won't the following code display the cell. The line inside the "IF" complains that "A value used in the formula is of the wrong type". I've tried many variations of this but this one I think gets the point across. Function CopyNumberWithOneUnderlinedCharacter(ByVal myCell As Range) Dim l_Position As Integer For l_Position = 1 To Len(myCell.Value) If myCell.Characters(Start:=l_Position, Length:=1).Font.Underline _ < xlUnderlineStyleNone Then CopyNumberWithOneUnderlinedCharacter.Characters(St art:=l_Position, Length:=1).Font.Underline = xlUnderlineStyleSingle End If Next l_Position End Function |
All times are GMT +1. The time now is 09:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com