Need func to display a cell + formatting in another cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
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.
 
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
 
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?
 
Back
Top