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)
Hi Adriano,
Can you please upload a sample file with a manual result showing how the blocks should look like based on the block description you provided?
It will help me understand the exact structure you described in your scenario.
Hi Catalin,
I uploaded a spreadsheet with more details about the spreadsheet.
Thanks
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
Hi Catalin,
I'm starting to study VBA.
Can you help me create this code?
Thank you.
Yes, I can help you, but clarify first the issues described above.
Thank you
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
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?
Hi Catalin,
I found it easier to resend the worksheet with the drawing with the explanation.
With the drawing (before and after) it will be easier to understand.
Thank you
