Is there any way to solve this problem ?
LOL! Never occurred to me!
--
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
"/-_-b" wrote in message
...
LOL. Nah. I'm just saying thank you.
"Bob Phillips" wrote in message
...
I see you use Ty, what does it mean? Is it some forma of Ta?
--
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my
addy)
"/-_-b" wrote in message
...
Ty Bob , this works just fine !
"Bob Phillips" wrote in message
...
Ooops, what a gaffe!
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
Else
.Cells(i, TEST_COLUMN).Value = Chr(letter) & ") " &
.Cells(i, TEST_COLUMN).Value
letter = letter + 1
End If
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
...
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 !
|