Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there any way to solve this problem ?
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 ! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there any way to solve this problem ?
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 ! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there any way to solve this problem ?
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 ! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there any way to solve this problem ?
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 ! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there any way to solve this problem ?
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 ! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CAN ANYONE PLEASE SOLVE MY PROBLEM | Excel Programming | |||
Please solve this problem. | Excel Worksheet Functions | |||
How to solve this problem? | Excel Programming | |||
Problem when multipple users access shared xl-file at the same time, macrocode for solve this problem? | Excel Programming | |||
A problem to solve, a little help | Excel Programming |