need help replacing string with "."
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 |
need help replacing string with "."
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 |
need help replacing string with "."
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 |
need help replacing string with "."
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 |
need help replacing string with "."
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 |
need help replacing string with "."
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 |
All times are GMT +1. The time now is 11:07 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com