Is there any way to solve this problem ?
Thank you Bob, everything works great ... except the code starts to put a)
in front of the question, instead starting from the first answer to the
question. Can this be fixed ? The questions are in bold, maybe that could
help.
Ty
Marko
"Bob Phillips" wrote in message
...
Public Sub ProcessData()
Const TEST_COLUMN As String = "B" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim letter As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To LastRow
If .Cells(i, TEST_COLUMN).Offset(0, -1).Value < "" Then
letter = 97
End If
.Cells(i, TEST_COLUMN).Value = Chr(letter) & ") " & .Cells(i,
TEST_COLUMN).Value
letter = letter + 1
Next i
For i = LastRow - 1 To 1 Step -1
If .Cells(i + 1, TEST_COLUMN).Offset(0, -1).Value < "" Then
.Rows(i + 1).Resize(2).Insert
End If
Next i
End With
End Sub
--
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my
addy)
"/-_-b" wrote in message
...
Hi all!
I got a problem with my macro... this is basically macro for sorting
questions and answers from 2 sheets into a new one. The result looks like
this :
-----------------------------------------------------------------------------------
| A | B | C |
-----------------------------------------------------------------------------------
| 1 | Who was the first... | |
-----------------------------------------------------------------------------------
| | Lincoln | |
-------------------------------------------------------------------------------------
| | Washington | + |
-------------------------------------------------------------------------------------
| | Clinton | |
--------------------------------------------------------------------------------------
| 2 | The only animal... | |
----------------------------------------------------------------------------------------
| | leopard | |
----------------------------------------------------------------------------------------
.....
My question : is there any way to insert :
1) a), b), c), d)... before the answers
2) blank row indent between 2 questions
to make one more sheet with the correct answers, ex. :
1 C, 2 B, 3 D and so on..
Here's my code :
Sub macro1()
'
' gsnuxx
' rev 1
'
k = 1
Set q = Sheets("questions")
Set a = Sheets("answers")
Set qa = Sheets("qa")
nq = q.Cells(Rows.Count, "A").End(xlUp).Row
na = a.Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To nq
n = q.Cells(i, 1).Value
qa.Cells(k, 1).Value = n
qa.Cells(k, 2).Value = q.Cells(i, 2).Value
qa.Cells(k, 2).Font.Bold = True
k = k + 1
For j = 1 To na
m = a.Cells(j, 1).Value
If m = n Then
qa.Cells(k, 2).Value = a.Cells(j, 2).Value
If a.Cells(j, 3).Value = 0 Then
qa.Cells(k, 3).Value = " "
Else
qa.Cells(k, 3).Value = "+"
End If
k = k + 1
End If
Next
Next
End Sub
Ty in advance !
|