
Active Member

July 30, 2022

I'm working with a macro (contributed by a colleague on the forum) that inserts a line below whenever the answer is PC or NC.
====
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("V:V")) Is Nothing Then
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
End If
On Error GoTo 0
If Target.Column <> 22 Or Target.Text <> "NC" And Target.Text <> "PC" Then Exit Sub
Rows(Target.Row + 1).Insert
Cells(Target.Row + 1, 23).Resize(, 42).Merge
Cells(Target.Row + 1, 23).Select
Cells(Target.Row + 1, 22).ClearContents
End Sub
====
How should be
--------------------
Block 1
---------------------
Question 1 = C
Question 2 = NC (Since the answer was NC, insert a line below question 2)
Question 3 = C
Question 4 = C
Question 5 = PC (Since the answer was PC, insert a line below question 5)
(As PC or NC answers appeared in this block, insert one more line below question 5)
---------------------
Block 2
---------------------
Question 1 = C
Question 2 = C
Question 3 = NC (Since the answer was NC, insert a line below question 3)
(As PC or NC answers appeared in this block, insert one more line below question 3)
---------------------
Block 3
---------------------
Question 1 = C
Question 2 = PC (Since the answer was PC, insert a line below question 2)
Question 3 = C
Question 4 = C
(As PC or NC answers appeared in this block, insert a line below question 4)


November 8, 2013

Hi Adriano,
Let's look closer at block 1 and 2 in your example:
-in block 1 you have 1 NC, and the code is supposed to add 1 row below the NC, AND another row under block 1.
-in block 2, you have 1 PC and 2 NC, your example shows 1 row added under each of these 3 PC-NC, but no additional rows under block 2.
You have to make the rules more clear, the current description don't match the example. The description says that when a user types PC in block 2, the code should insert a row under the edited line, AND another row under the current block.
With these rules, if a user types 3 PC-NC in a block, the code will add 3 rows under the 3 added PC/NC's, and 3 rows under the block. In you example block 2, I see only the 3 added rows under each PC/NC, but nothing under the block.
More rules are needed, unfortunately.
Thank you

Active Member

July 30, 2022

Hi Catalin
follow more
explanations about the spreadsheet.
It is a worksheet is made up of blocks of questions. For each question, answers must be given.
The answers can be: C (conform); PC (partially compliant) or NC (non-compliant).
If the answer to a question is PC or NC, a line must be inserted below the question for a comment to be entered.
If the answer to a question is C, it is not necessary to insert any lines below the question.
At the end of the block, check:
If there was any NC or PC response in the block, a line must be inserted at the end of the block.
Thanks


November 8, 2013

Hi Adriano,
In the block 2 you sent, I see a new row ONLY under those 3 PC/NC, but no rows under the block:
According to your description, if in that block there is a PC/NC, we should insert 2 rows, one under the current row and one under the block. Block 2 has 3 PC/NC, but I see only 3 new rows, not 6 as you wanted. Based on your description, block 2 should look like this:
Why is that?
1 Guest(s)
