i used a macro to replace the "." but can someone tell me how to replace
"."and the text following the period. i mean when ever it hits a period it
should delete everything thats after the "."
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range

Set WB = Workbooks("MasterList.xls")
Set SH = WB.Sheets("VT Masterlist") '
Set rng = SH.Range("A:A")


For Each rCell In rng.Cells
With rCell
If Not IsEmpty(.Value) Then
.Replace What:=".", Replacement:=""
End If
End With
Next rCell

End Sub

For Each rcell In rng.Cells
With rcell
ipos = InStr(rcell.Value, ".")
If ipos 0 Then
rcell.Value = Left(rcell.Value, ipos - 1)
End If
End With
Next rcell


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Arain" wrote in message
...
i used a macro to replace the "." but can someone tell me how to replace
"."and the text following the period. i mean when ever it hits a period
it
should delete everything thats after the "."
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range

Set WB = Workbooks("MasterList.xls")
Set SH = WB.Sheets("VT Masterlist") '
Set rng = SH.Range("A:A")


For Each rCell In rng.Cells
With rCell
If Not IsEmpty(.Value) Then
.Replace What:=".", Replacement:=""
End If
End With
Next rCell

End Sub

I am going with the bulky approach. God, your code is so compact.

I will do:
Dim temp
temp = Slip(ActiveCell.FormulaC1R1, ".")
ActiveCell.FormulaC1R1 = temp(0)


This simply set the formula to anything that's before the "."


"Arain" wrote:

i used a macro to replace the "." but can someone tell me how to replace
"."and the text following the period. i mean when ever it hits a period it
should delete everything thats after the "."
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range

Set WB = Workbooks("MasterList.xls")
Set SH = WB.Sheets("VT Masterlist") '
Set rng = SH.Range("A:A")


For Each rCell In rng.Cells
With rCell
If Not IsEmpty(.Value) Then
.Replace What:=".", Replacement:=""
End If
End With
Next rCell

End Sub

Thank you guys great help

"jchen" wrote:

I am going with the bulky approach. God, your code is so compact.

I will do:
Dim temp
temp = Slip(ActiveCell.FormulaC1R1, ".")
ActiveCell.FormulaC1R1 = temp(0)


This simply set the formula to anything that's before the "."


"Arain" wrote:

i used a macro to replace the "." but can someone tell me how to replace
"."and the text following the period. i mean when ever it hits a period it
should delete everything thats after the "."
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range

Set WB = Workbooks("MasterList.xls")
Set SH = WB.Sheets("VT Masterlist") '
Set rng = SH.Range("A:A")


For Each rCell In rng.Cells
With rCell
If Not IsEmpty(.Value) Then
.Replace What:=".", Replacement:=""
End If
End With
Next rCell

End Sub

Another way...

If you select column A and do
edit|replace
what: *. (asterisk, dot)
with: (leave blank)
replace all

You can get all of the cells in one fell swoop.

In code:

sh.Range("A:A").Replace What:=".*", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Arain wrote:

i used a macro to replace the "." but can someone tell me how to replace
"."and the text following the period. i mean when ever it hits a period it
should delete everything thats after the "."
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range

Set WB = Workbooks("MasterList.xls")
Set SH = WB.Sheets("VT Masterlist") '
Set rng = SH.Range("A:A")


For Each rCell In rng.Cells
With rCell
If Not IsEmpty(.Value) Then
.Replace What:=".", Replacement:=""
End If
End With
Next rCell

End Sub

--

Dave Peterson

That first routine removed the dot and everything following it. If you wanted
to keep the period:

SH.Range("a:a").Replace What:=".*", Replacement:=".", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False



Arain wrote:

i used a macro to replace the "." but can someone tell me how to replace
"."and the text following the period. i mean when ever it hits a period it
should delete everything thats after the "."
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range

Set WB = Workbooks("MasterList.xls")
Set SH = WB.Sheets("VT Masterlist") '
Set rng = SH.Range("A:A")


For Each rCell In rng.Cells
With rCell
If Not IsEmpty(.Value) Then
.Replace What:=".", Replacement:=""
End If
End With
Next rCell

End Sub

--

Dave Peterson