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

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

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