Why does my Excel Macro not work?

axeanddeer 1 Reputation point
2021-11-01T13:59:19.727+00:00

I am relatively new to Macros, so this code may look really bad.
What I am trying to do is:

I have a table with one column which looks like this:

I am text with a dot.
I am text without a dot
and I am also a text without a dot
so I want to be added.
I am a text with a dot.

The result should be:
I am text with a dot
I am text without a dot and I am also a text without a dot so I want to be added.

I am a text with a dot.

Meaning - All the cells below each other that do not have a dot should be summarized into one string, making the first cell the whole string and leaving the other cells blank.

What I tried is this, which simply results in Excel crashing:

Sub Rectify()

Dim Chain As String
Dim x As Integer
Dim y As Integer

x = 1

Do Until x > 5
Chain = Cells(x, "A").Value
y = x

Do Until Right(Cells(x, "A"), 1) = "."
    Chain = Chain + " " + Cells(x, 1).Value
    Cells(x, "A") = " "
    x = x + 1
Loop

Loop

End Sub

I come from a different programming language and am rusty - I hope someone can help me make it work.

Microsoft 365 and Office | Development | Other
Developer technologies | Visual Basic for Applications
0 comments No comments
{count} votes

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.