If test is true, select a range and move all below down-2

[PaulCruice]

GreekWord1 EnglishWord1 Notes1
GreekWord2 EnglishWord2 Notes2
, EnglishWord3 Notes3
GreekWord3 EnglishWord4 Notes4
GreekWord4 EnglishWord5 Notes5
. EnglishWord6 Notes6
GreekWord5 EnglishWord7 Notes7
GreekWord6 EnglishWord8 Notes8
? EnglishWord9 Notes9
GreekWord7
GreekWord8
GreekWord9

The example above loses its formatting here but I think you will get the drift.
Column A has a list of Greek words WITH punctuation in separate cells. Column B & C are the English translation and notes for each Greek word. What I want to do is insert blank cells in Columns B & C to correspond with the punctuation so that the English words and notes properly align with their correct Greek word. My attempt at a macro (below) does not work.

Sub Macro1()
Dim x As Integer
For x = 1 To 9
If RxC1 = "," Then
Range(Cells(x, 2), Cells(x, 3)).Select
Selection.Insert Shift:=xlDown
ElseIf RxC1 = "." Then
Range(Cells(x, 2), Cells(x, 3)).Select
Selection.Insert Shift:=xlDown
ElseIf RxC1 = "?" Then
Range(Cells(x, 2), Cells(x, 3)).Select
Selection.Insert Shift:=xlDown
End If
Next x
End Sub

 

[pgc01]

Hi PaulCruice

Assuming the data start in A1, try:

Sub PunctIns()
Dim rRng As Range, rCell As Range

Set rRng = Range("A1", Range("A" & Rows.Count).End(xlUp))

For Each rCell In rRng
    If rCell = "." Or rCell = "," Or rCell = "?" Then _
        rCell.Offset(, 1).Resize(, 2).Insert
Next rCell
End Sub

Hope this helps
PGC

 

[PaulCruice]

pgc01,
Thanks for the reply but I get a syntax error when I try to compile this. Since I'm not any sort of a programmer I don't know where to start looking to rectify it.
Cheers.

 

[pgc01]

Hi PaulCruice

I've just retried the code and got no problem.

I'll tell exactly what I did. Maybe this helps.

1 - I excuted excel creating a new workbook
2 - Copied the data in your post to excel with paste special text

Got this:

Book1
	A	B	C	D
1	GreekWord1EnglishWord1Notes1
2	GreekWord2EnglishWord2Notes2
3	,EnglishWord3Notes3
4	GreekWord3EnglishWord4Notes4
5	GreekWord4EnglishWord5Notes5
6	.EnglishWord6Notes6
7	GreekWord5EnglishWord7Notes7
8	GreekWord6EnglishWord8Notes8
9	?EnglishWord9Notes9
10	GreekWord7
11	GreekWord8
12	GreekWord9
Sheet1

3 - Used Data>Text to Columns to separe the data.

Book1
	A	B	C	D
1	GreekWord1	EnglishWord1	Notes1
2	GreekWord2	EnglishWord2	Notes2
3	,	EnglishWord3	Notes3
4	GreekWord3	EnglishWord4	Notes4
5	GreekWord4	EnglishWord5	Notes5
6	.	EnglishWord6	Notes6
7	GreekWord5	EnglishWord7	Notes7
8	GreekWord6	EnglishWord8	Notes8
9	?	EnglishWord9	Notes9
10	GreekWord7
11	GreekWord8
12	GreekWord9
13
Sheet1

4 - Called the vba editor (ALT F11 or Tools>Macros>Visula Basic Editor, or right-click on the worksheet tab and choose View Code)

5 - Right-clicked on the workbook's project, in my case VBAProject(Book1) and chose insert>Module. Excel created a module called Module1.

6 - Pasted into Module1 the code I posted before.

7 - Ran the code. Got the following result:

Book1
	A	B	C	D
1	GreekWord1	EnglishWord1	Notes1
2	GreekWord2	EnglishWord2	Notes2
3	,
4	GreekWord3	EnglishWord3	Notes3
5	GreekWord4	EnglishWord4	Notes4
6	.
7	GreekWord5	EnglishWord5	Notes5
8	GreekWord6	EnglishWord6	Notes6
9	?
10	GreekWord7	EnglishWord7	Notes7
11	GreekWord8	EnglishWord8	Notes8
12	GreekWord9	EnglishWord9	Notes9
13
Sheet1

I hope this helps. If not, please post details. Which line, which compile error message, etc.

Kind regards
PGC

 

[PaulCruice]

PGC,
Thanks indeed for the help.
Paul