Need func to display a cell + formatting in another cell

[Guest]

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, when invoked in sheet 2 such as
"=CopyNumberWithOneUnderlinedCharacter(Sheet1!A2)", 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(Start:=l_Position,
Length:=1).Font.Underline = xlUnderlineStyleSingle
End If
Next l_Position
End Function

 

[Dave Peterson]

You're trying to return just that one character?

Option Explicit
Function CopyNumberWithOneUnderlinedCharacter(ByVal myCell As Range)
Dim l_Position As Integer
CopyNumberWithOneUnderlinedCharacter = ""
For l_Position = 1 To Len(myCell.Value)
If myCell.Characters(Start:=l_Position, Length:=1).Font.Underline _
= xlUnderlineStyleSingle Then
CopyNumberWithOneUnderlinedCharacter _
= Mid(myCell.Value, l_Position, 1)
Exit Function
End If
Next l_Position
End Function

But you'll just see the character--not the underscore.

 

[Guest]

Dave,

I really am looking to do programmatically what you would do with copy and
paste. I want the target cell to include an exact copy of the source cell. I
tried:
activate the worksheet
myCell.Select
ActiveSelection.Copy
activate second worksheet
select target range
ActiveWorkSheet.Paste

That didn't work either. I want the whole source cell with all its
formatting, that's why the original code loops through the characters. I have
tried this so many ways that I need someone to try it out and show me what
the code is. I'm out of ideas on this one.

Thanks.

 

[Guest]

Dave,

Sorry, Ive tried so many versions that I'm confused. I want to copy the
entire value and then underline to match original. What I have below is
better but still does not underline. I hardcoded the destination cell because
I could use CopyNumberWithOneUnderlinedCharacter where I have
Worksheets("Sheet2").Cells(1, 1).

Function CopyNumberWithOneUnderlinedCharacter(ByVal myCell As Range)
Dim l_Position As Integer

'Copy entire value
CopyNumberWithOneUnderlinedCharacter = myCell.Value

'Convert to text format so underline is visible
Worksheets("Sheet2").Cells(1, 1).NumberFormat = "@"

'Duplicate the underlining
For l_Position = 1 To Len(myCell.Value)
If myCell.Characters(Start:=l_Position, Length:=1).Font.Underline _
= xlUnderlineStyleSingle Then
Worksheets("Sheet2").Cells(1, 1).Characters(Start:=l_Position,
Length:=1).Font.Underline _
= xlUnderlineStyleSingle
Exit Function
End If
Next l_Position
End Function

 

[Dave Peterson]

How are you using your function?

If you're using it from a cell in a worksheet, then you have a problem. You
can't format formulas (or numbers) this way (character by character).

And you can't change format ("@") when you use a function from a worksheet. The
best you can do is return a value.

Options would include some Subroutine--maybe invoked from a worksheet_change
event?