Hello,

Im trying to play with this code.
i'm having problems with the second IF part.

thanks for your help in advance.

Private Sub Worksheet_Change(ByVal Target As Range)

pinput = Target.Value
' this changes the value from 330p to 3:30 PM
' this works
If Right(pinput, 1) = "p" And Len(pinput) 2 Then
newvalue = Left(Left(pinput, Len(pinput) - 1), Len(Left(pinput,
Len(pinput) - 1)) - 2) & ":" & Right(Left(pinput, Len(pinput) - 1), 2) & "
PM"
Target.Value = newvalue

Else
' this should for example change from 2p to 2:00 PM
' it does not work. it does nothing.

If Right(pinput, 1) = "p" And Len(pinput) < 2 Then
newvalue = Left(pinput, Len(pinput) - 1) & ":00" & " PM"
Target.Value = newvalue


End If
End If

End Sub


Thanks,

Hi
change the lines
----
Else
' this should for example change from 2p to 2:00 PM
' it does not work. it does nothing.

If Right(pinput, 1) = "p" And Len(pinput) < 2 Then
newvalue = Left(pinput, Len(pinput) - 1) & ":00" & " PM"
Target.Value = newvalue
End If
----
to
----
ElseIf Right(pinput, 1) = "p" And Len(pinput) <= 2 Then
newvalue = Left(pinput, Len(pinput) - 1) & ":00" & " PM"
Target.Value = newvalue
---
Two changes:
- combination of Else and If statement
- Change <2 to <=2


--
Regards
Frank Kabel
Frankfurt, Germany

Cesar Zapata wrote:
Hello,

Im trying to play with this code.
i'm having problems with the second IF part.

thanks for your help in advance.

Private Sub Worksheet_Change(ByVal Target As Range)

pinput = Target.Value
' this changes the value from 330p to 3:30 PM
' this works
If Right(pinput, 1) = "p" And Len(pinput) 2 Then
newvalue = Left(Left(pinput, Len(pinput) - 1), Len(Left(pinput,
Len(pinput) - 1)) - 2) & ":" & Right(Left(pinput, Len(pinput) - 1),
2) & " PM"
Target.Value = newvalue

Else
' this should for example change from 2p to 2:00 PM
' it does not work. it does nothing.

If Right(pinput, 1) = "p" And Len(pinput) < 2 Then
newvalue = Left(pinput, Len(pinput) - 1) & ":00" & " PM"
Target.Value = newvalue


End If
End If

End Sub


Thanks,

Hi Cesar,

Here's a shot

Private Sub Worksheet_Change(ByVal Target As Range)
Dim pinput, newvalue

Application.EnableEvents = False
On Error GoTo ws_exit
pinput = Target.Value
' this changes the value from 330p to 3:30 PM
' this works
If Right(pinput, 1) = "p" And Len(pinput) 3 Then
newvalue = Left(Left(pinput, Len(pinput) - 1), _
Len(Left(pinput, Len(pinput) - 1)) - 2) & ":" & _
Right(Left(pinput, Len(pinput) - 1), 2) & "PM "
Target.Value = newvalue
Else
' this should for example change from 2p to 2:00 PM
' it does not work. it does nothing.

If Right(pinput, 1) = "p" And Len(pinput) < 4 Then
newvalue = Left(pinput, Len(pinput) - 1) & ":00" & " PM"
Target.Value = newvalue
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Cesar Zapata" wrote in message
...
Hello,

Im trying to play with this code.
i'm having problems with the second IF part.

thanks for your help in advance.

Private Sub Worksheet_Change(ByVal Target As Range)

pinput = Target.Value
' this changes the value from 330p to 3:30 PM
' this works
If Right(pinput, 1) = "p" And Len(pinput) 2 Then
newvalue = Left(Left(pinput, Len(pinput) - 1), Len(Left(pinput,
Len(pinput) - 1)) - 2) & ":" & Right(Left(pinput, Len(pinput) - 1), 2) & "
PM"
Target.Value = newvalue

Else
' this should for example change from 2p to 2:00 PM
' it does not work. it does nothing.

If Right(pinput, 1) = "p" And Len(pinput) < 2 Then
newvalue = Left(pinput, Len(pinput) - 1) & ":00" & " PM"
Target.Value = newvalue


End If
End If

End Sub


Thanks,