If test true, insert cells moving down

[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 above made-up example above may lose its formatting here but I think you will get the drift. Column A has a list of 9 Greek words WITH punctuation in separate cells making a total of 12 cells in column A. 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. It is essential I be able to identify specific punctuation marks. I note that some Greek words are only one letter long. I am not a programmer and so my attempt at the macro below which does not work.

Sub Macro1()
Dim x As Integer
For x = 1 To 12
If RxC1 = "," Or RxC1 = "." Or RxC1 = "?" Then 'test if punctuation occurs
Range(Cells(x, 2), Cells(x, 3)).Select 'if punctuation occurs select cells in columns 2 & 3 for row in question
Selection.Insert Shift:=xlDown 'insert cells moving all cells down
End If
Next x
End Sub

 

[iggydarsa]

Hi Paul, I think you are on the right track but instead of this line

If RxC1 = "," Or RxC1 = "." Or RxC1 = "?" Then 'test if punctuation occurs

try this:

If (Range("A" & x).Value = "." Or Range("A" & x).Value = "," Or Range("A" & x).Value = "?") Then 'test if punctuation occurs


In fact you code should look something like this:

Sub Macro1()
Dim x As Integer
For x = 1 To 12
If (Range("A" & x).Value = "." Or Range("A" & x).Value = "," Or Range("A" & x).Value = "?") Then 'test if punctuation occurs
    Range(Cells(x, 2), Cells(x, 3)).Insert Shift:=xlDown
End If
Next x
End Sub

 

[PaulCruice]

iggydarsa,
Thanks so much. Works like a charm. Very much appreciated.
All the best.
Paul

 

[HalfAce]

Hello PaulCruice
Another method for this would be to use select case.(ie.)

Sub ItsAllGreekToMe()

Dim LstRw As Long, Rw As Long
LstRw = Cells(Rows.Count, "A").End(xlUp).Row

For Rw = 1 To LstRw
  Select Case Cells(Rw, "A").Value
    Case "."
      Cells(Rw, "A").Offset(, 1).Resize(, 2).Insert Shift:=xlDown
    Case ","
      Cells(Rw, "A").Offset(, 1).Resize(, 2).Insert Shift:=xlDown
    Case "?"
      Cells(Rw, "A").Offset(, 1).Resize(, 2).Insert Shift:=xlDown

    'Add any other cases you like here

  End Select
Next Rw
End Sub

 

[PaulCruice]

HalfAce,
Thanks also. My next task was to find out how to automatically find the Last Row in a column and you gave me that. So thanks heaps.
Paul