I got a very good answer to a question in here just the other day, and it
works great! I'm trying to add to it now though, and can't figure out how
to get it to work.

Here's the code Mr. Ogilvy so graciously supplied, which as I said works
great:

Public Function NoDash(rng As Range)

Dim sStr As String
sStr = ""
For Each cell In rng
sStr = sStr & cell.Value & "," & Chr(10)
Next
sStr = Left(sStr, Len(sStr) - 2)
NoDash = sStr

End Function

However, for the cell to be "formatted" correctly, it needs to have the wrap
text attribute set to true.

I use a second function to strip just the portion after the dash(if it
exists) as well, like this:

Function RightOfDash(S As String) As String

Dim Pos As Integer
Pos = InStr(1, S, "-", vbTextCompare)
If Pos 0 Then
RightOfDash = Mid(S, Pos + 1)
Else
RightOfDash = S
End If
End Function

What I'd like to do is us an if statement in the cell to decide which of
these to run, something like this:
=(IF(ISNUMBER(FIND("-",'Veh 1 Data'!J1)),rightofdash('Veh 1
Data'!J1)&","&rightofdash('Veh 1 Data'!J2)&","&rightofdash('Veh 1
Data'!J3)&","&rightofdash('Veh 1 Data'!J4),NoDash('Veh 1 Data'!J1:J4)))

But, I can't figure out how to turn the wrap text attribute on if the dash
doesn't exist, and turn it off if it does. I've tried using :
Worksheets("Tire Wear Summary").Range("C15").WrapText = (then set to true or
false, depending on which function it's in), but this has no effect.

Is there any way to accomplish this? If not, it's no big deal, I can keep
the two as separate workbooks and run the appropriate one, but if I can
somehow set the wrap attribute on the fly as it were, I can combine the two
into one workbook.

Thanks!
Shawn

Worksheets("Tire Wear Summary").Range("C15").WrapText = (then set to
true or
false, depending on which function it's in),


That code works for me, are you sure those references are right? Show
us your code.

You have a problem. You cannot set attributes of a cell within a UDF, you
can only return a result.

You need to think of an alternative solution.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"43fan" wrote in message
...
I got a very good answer to a question in here just the other day, and it
works great! I'm trying to add to it now though, and can't figure out how
to get it to work.

Here's the code Mr. Ogilvy so graciously supplied, which as I said works
great:

Public Function NoDash(rng As Range)

Dim sStr As String
sStr = ""
For Each cell In rng
sStr = sStr & cell.Value & "," & Chr(10)
Next
sStr = Left(sStr, Len(sStr) - 2)
NoDash = sStr

End Function

However, for the cell to be "formatted" correctly, it needs to have the
wrap
text attribute set to true.

I use a second function to strip just the portion after the dash(if it
exists) as well, like this:

Function RightOfDash(S As String) As String

Dim Pos As Integer
Pos = InStr(1, S, "-", vbTextCompare)
If Pos 0 Then
RightOfDash = Mid(S, Pos + 1)
Else
RightOfDash = S
End If
End Function

What I'd like to do is us an if statement in the cell to decide which of
these to run, something like this:
=(IF(ISNUMBER(FIND("-",'Veh 1 Data'!J1)),rightofdash('Veh 1
Data'!J1)&","&rightofdash('Veh 1 Data'!J2)&","&rightofdash('Veh 1
Data'!J3)&","&rightofdash('Veh 1 Data'!J4),NoDash('Veh 1 Data'!J1:J4)))

But, I can't figure out how to turn the wrap text attribute on if the dash
doesn't exist, and turn it off if it does. I've tried using :
Worksheets("Tire Wear Summary").Range("C15").WrapText = (then set to true
or
false, depending on which function it's in), but this has no effect.

Is there any way to accomplish this? If not, it's no big deal, I can keep
the two as separate workbooks and run the appropriate one, but if I can
somehow set the wrap attribute on the fly as it were, I can combine the
two
into one workbook.

Thanks!
Shawn

Chip,

I included the entire code I'm using in the previous message. The only
thing I didn't include was where I'd tried inserting the "Worksheets..."
statement into the code. I tried it both at the beginning and the end of
both functions(RightOfDash and NoDash). The worksheet name is correct, as
is the cell I want to format. Actually, I want to format a range of
cells(C15-I15) but figured I'd try it just with the one cell to begin with.

The code itself for formatting the cell I'm pretty sure is correct, yes, as
I got it pretty much straight from the Help area. But I can't get it to
work within the context I'm trying to use it.

Thanks!
Shawn

"Chip" wrote in message
ups.com...
Worksheets("Tire Wear Summary").Range("C15").WrapText = (then set to
true or
false, depending on which function it's in),


That code works for me, are you sure those references are right? Show
us your code.

Public Function DashOrNot(rng As Range)
Dim sStr1 as String
Dim sStr As String
Dim cell as Range
Dim bDash as Boolean
sStr = ""
For Each cell In rng
sStr1 = cell.Value
bDash = False
if instr(sStr1,"-") then
sStr1 = Mid(sStr1,instr(sStr1)+1)
bDash = True
end if
sStr = sStr & Trim(sStr1) & "," & _
iif(bDash,vbNullChar,chr(10))
Next
sStr = Left(sStr, Len(sStr) - 2)
DashOrNot = sStr
End Function

Should handle either situation, but won't solve your wrap problem - unless
you make the column wide enough so the string won't wrap unless it contains
the chr(10) in it. Then you could format the column for wraptext and it
wouldn't affect the dash rows.

--
Regards,
Tom Ogilvy






"43fan" wrote in message
...
I got a very good answer to a question in here just the other day, and it
works great! I'm trying to add to it now though, and can't figure out how
to get it to work.

Here's the code Mr. Ogilvy so graciously supplied, which as I said works
great:

Public Function NoDash(rng As Range)

Dim sStr As String
sStr = ""
For Each cell In rng
sStr = sStr & cell.Value & "," & Chr(10)
Next
sStr = Left(sStr, Len(sStr) - 2)
NoDash = sStr

End Function

However, for the cell to be "formatted" correctly, it needs to have the
wrap
text attribute set to true.

I use a second function to strip just the portion after the dash(if it
exists) as well, like this:

Function RightOfDash(S As String) As String

Dim Pos As Integer
Pos = InStr(1, S, "-", vbTextCompare)
If Pos 0 Then
RightOfDash = Mid(S, Pos + 1)
Else
RightOfDash = S
End If
End Function

What I'd like to do is us an if statement in the cell to decide which of
these to run, something like this:
=(IF(ISNUMBER(FIND("-",'Veh 1 Data'!J1)),rightofdash('Veh 1
Data'!J1)&","&rightofdash('Veh 1 Data'!J2)&","&rightofdash('Veh 1
Data'!J3)&","&rightofdash('Veh 1 Data'!J4),NoDash('Veh 1 Data'!J1:J4)))

But, I can't figure out how to turn the wrap text attribute on if the dash
doesn't exist, and turn it off if it does. I've tried using :
Worksheets("Tire Wear Summary").Range("C15").WrapText = (then set to true
or
false, depending on which function it's in), but this has no effect.

Is there any way to accomplish this? If not, it's no big deal, I can keep
the two as separate workbooks and run the appropriate one, but if I can
somehow set the wrap attribute on the fly as it were, I can combine the
two
into one workbook.

Thanks!
Shawn

Well that explains why it doesn't work then. ;) Thanks Bob.

Any ideas? I guess I could write a small macro to reformat the page, and
put a button or something on the page for the user to click to do it.
*shrug*


"Bob Phillips" wrote in message
...
You have a problem. You cannot set attributes of a cell within a UDF, you
can only return a result.

You need to think of an alternative solution.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"43fan" wrote in message
...
I got a very good answer to a question in here just the other day, and
it
works great! I'm trying to add to it now though, and can't figure out
how
to get it to work.

Here's the code Mr. Ogilvy so graciously supplied, which as I said works
great:

Public Function NoDash(rng As Range)

Dim sStr As String
sStr = ""
For Each cell In rng
sStr = sStr & cell.Value & "," & Chr(10)
Next
sStr = Left(sStr, Len(sStr) - 2)
NoDash = sStr

End Function

However, for the cell to be "formatted" correctly, it needs to have the
wrap
text attribute set to true.

I use a second function to strip just the portion after the dash(if it
exists) as well, like this:

Function RightOfDash(S As String) As String

Dim Pos As Integer
Pos = InStr(1, S, "-", vbTextCompare)
If Pos 0 Then
RightOfDash = Mid(S, Pos + 1)
Else
RightOfDash = S
End If
End Function

What I'd like to do is us an if statement in the cell to decide which of
these to run, something like this:
=(IF(ISNUMBER(FIND("-",'Veh 1 Data'!J1)),rightofdash('Veh 1
Data'!J1)&","&rightofdash('Veh 1 Data'!J2)&","&rightofdash('Veh 1
Data'!J3)&","&rightofdash('Veh 1 Data'!J4),NoDash('Veh 1 Data'!J1:J4)))

But, I can't figure out how to turn the wrap text attribute on if the
dash
doesn't exist, and turn it off if it does. I've tried using :
Worksheets("Tire Wear Summary").Range("C15").WrapText = (then set to
true
or
false, depending on which function it's in), but this has no effect.

Is there any way to accomplish this? If not, it's no big deal, I can
keep
the two as separate workbooks and run the appropriate one, but if I can
somehow set the wrap attribute on the fly as it were, I can combine the
two
into one workbook.

Thanks!
Shawn

I would use event code myself so that when the value is entered in J1, it
checks it and returns the value you want in a cell. I have set this up to
return the value in H1. Not ideal, but may work for you

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iPos As Long
Const FormulaCell As String = "H1"

On Error Resume Next
Application.EnableEvents = False
If Target.Address = "$J$1" Then
iPos = Application.Find("-", Target.Value)
On Error GoTo ws_exit:
With Me.Range(FormulaCell)
If iPos = 0 Then
.WrapText = True
.Value = NoDash(Target.Resize(4, 1))
Else
.WrapText = False
.Value = RightOfDash(.Value) & "," & _
RightOfDash(.Offset(1, 0).Value) & "," & _
RightOfDash(.Offset(2, 0).Value) & "," & _
RightOfDash(.Offset(3, 0).Value)
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.





--

HTH

RP
(remove nothere from the email address if mailing direct)


"43fan" wrote in message
...
Well that explains why it doesn't work then. ;) Thanks Bob.

Any ideas? I guess I could write a small macro to reformat the page, and
put a button or something on the page for the user to click to do it.
*shrug*


"Bob Phillips" wrote in message
...
You have a problem. You cannot set attributes of a cell within a UDF,
you
can only return a result.

You need to think of an alternative solution.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"43fan" wrote in message
...
I got a very good answer to a question in here just the other day, and
it
works great! I'm trying to add to it now though, and can't figure out
how
to get it to work.

Here's the code Mr. Ogilvy so graciously supplied, which as I said
works
great:

Public Function NoDash(rng As Range)

Dim sStr As String
sStr = ""
For Each cell In rng
sStr = sStr & cell.Value & "," & Chr(10)
Next
sStr = Left(sStr, Len(sStr) - 2)
NoDash = sStr

End Function

However, for the cell to be "formatted" correctly, it needs to have
the
wrap
text attribute set to true.

I use a second function to strip just the portion after the dash(if it
exists) as well, like this:

Function RightOfDash(S As String) As String

Dim Pos As Integer
Pos = InStr(1, S, "-", vbTextCompare)
If Pos 0 Then
RightOfDash = Mid(S, Pos + 1)
Else
RightOfDash = S
End If
End Function

What I'd like to do is us an if statement in the cell to decide which
of
these to run, something like this:
=(IF(ISNUMBER(FIND("-",'Veh 1 Data'!J1)),rightofdash('Veh 1
Data'!J1)&","&rightofdash('Veh 1 Data'!J2)&","&rightofdash('Veh 1
Data'!J3)&","&rightofdash('Veh 1 Data'!J4),NoDash('Veh 1
Data'!J1:J4)))

But, I can't figure out how to turn the wrap text attribute on if the
dash
doesn't exist, and turn it off if it does. I've tried using :
Worksheets("Tire Wear Summary").Range("C15").WrapText = (then set to
true
or
false, depending on which function it's in), but this has no effect.

Is there any way to accomplish this? If not, it's no big deal, I can
keep
the two as separate workbooks and run the appropriate one, but if I
can
somehow set the wrap attribute on the fly as it were, I can combine
the
two
into one workbook.

Thanks!
Shawn