user3669801 user3669801 - 4 months ago 20
Vb.net Question

Copy and Inserting a Range If Cell Contains a Keyword

I am very new to

VB Macro
in
Excel
. I have a lots of data about questions and their anwsers in
excel
and unfortunately it is poorly formated. I am organizing the questions and answers.

I am trying to
Copy
a
Range
if cell
i
of column
N
contain keyword
Question
. If cell of column
N
contain keyword
"Question"
then I need to
copy
a
Range
from next
cell
to ninth
cell
of
column N
to
Column Y
. So, my range for copying is
Range(i+1, N : i+9, Y)
. Once, it is copied then I need to insert that
Range
after a
eighth cell
in
Column A
that has keyword
"Question"
. So, my inserting
Range
is
Range(i+9, A : i+17, A)
.

I tried the following code but it does not work. So, I would really appreciate any help regarding this. Following is my code:

Sub OrganizingQuestion()
Dim iRow As Integer
Dim que As String
Dim rng As Range
Dim rng2 As Range
For iRow = 1 To Range("N" & "5000").End(xlUp).Row Step 1
If Cells(iRow, 14) = "Question" Then
Set rng = Range("N" & iRow + 1 & ":Y" & iRow + 9)
rng.Copy
End If

If Cells(iRow, 1) = "Question" Then
Set rng2 = Range("A" & iRow + 9 & ":A" & iRow + 18)
rng2.EntireRow.Insert Shift:=xlDown, CopyOrigin:=rng
End If
Next iRow
End Sub

Answer

CopyOrigin is takes an XlInsertFormatOrigin enumeration value. I never use it.

enter image description here

Sub OrganizingQuestion()
    Dim iRow As Integer

    For iRow = 1 To Range("N" & Rows.Count).End(xlUp).Row Step 1

        If Cells(iRow, 14) = "Question" And Cells(iRow, 1) = "Question" Then
            Range("N" & iRow + 1 & ":Y" & iRow + 9).Copy
            Range("A" & iRow + 9 & ":A" & iRow + 18).Insert Shift:=xlDown
        End If

    Next iRow
End Sub