I have a large range of data in a column and some of the sentences do not have a period at the end of it and I need all of the sentences to have a period at the end before I import it to my database. This process will need to be done numerous times in the future so I want to write a script that will traverse through the column I need to, check to see if that sentence has a period or not and if not then add a period to the end.
Thanks,
Joe
Re: Adding period to end of sentences in cell range.
Welcome to OzGrid
Perhaps something like the following. It adds periods as the last character in each cell in the column with the curent active cell that does not currently end in a period.
Option Explicit
Sub AddPeriod()
Dim n As Long, LastRow As Long, c As Range, ActiveCol As Integer
ActiveCol = ActiveCell.Column
LastRow = Cells(65536, ActiveCol).End(xlUp).Row
For Each c In Range(Cells(1, ActiveCol), Cells(LastRow, ActiveCol))
If Right(c, 1) <> "." Then c = c & "."
Next c
End SubIf a given cell has more than one sentence in it, this code will fix the last sentence only.
Re: Adding period to end of sentences in cell range.
Adendum. I just realized that the code I posted will add a period to empty cells interspersed in the range of used cells. The following code avoids that.
Option Explicit
Sub AddPeriod()
Dim n As Long, LastRow As Long, c As Range, ActiveCol As Integer
ActiveCol = ActiveCell.Column
LastRow = Cells(65536, ActiveCol).End(xlUp).Row
For Each c In Range(Cells(1, ActiveCol), Cells(LastRow, ActiveCol))
If Len(c) > 0 And Right(c, 1) <> "." Then c = c & "."
Next c
End SubRe: Adding period to end of sentences in cell range.
Thank you that worked perfectly.
Joe
Don’t have an account yet? Register yourself now and be a part of our community!