CODEWITHSUNDEEP
excelvba
GenjutsuGhost
GenjutsuGhost

Reputation: 47

Excel VBA checking for dots

Hopefully, someone can help me with this!

I have a worksheet, in this worksheet, I have first names in column C.

So I need a script/macro which gets the first letter and put it in column B with a dot. (for example, the first name is Dave in column C, it needs to put the letter D in column B with a dot, for example, D.

Also, I would like that column B get checked so there aren't any double dots.

Is this even possible?

I know I can get the first letter with =LEFT(C1), but I'm trying to automate this with a script/macro. And I found also out I can create dots in front of the letters with:

Sub AddDotAfter()
    Dim cell As Range
    For Each cell In Selection
        If cell.Value <> "" Then
            cell.Value = cell.Value & "."
        Else: cell.Value = cell.Value
        End If
    Next cell
End Sub

Hope there is someone who can help me! Ty in advance!

Upvotes: 1

Views: 723

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57673

Check out the Range.Offset property in combination with the Left function:

Sub AddDotAfter()
    Dim cell As Range
    For Each cell In Selection
        If cell.Value <> "" Then
            cell.Offset(ColumnOffset:=-1).Value = Left$(cell.Value, 1) & "."
        End If
    Next cell
End Sub

Upvotes: 1

Related Questions