| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro problem - not working properly
Hey guys,
I got 2 sheets in my workbook from one of my web quizzes (both exported from mysql in excel format) sheet Q example: ---------------------------------------------------------------- | A | B | C | -------------------------------------------------- | 1 | Who was the first man on the Moon ? | category 1 | -------------------------------------------------- | 2 | The fastest animal on the planet is.. | category 3 | --- and so on... (A column - question no., B col. - question) sheet A example: ------------------------------------- | A | B | C | ------------------------------------- | 1 | John Glenn | 0 | ------------------------------------- | 1 | Neil Armstrong | 1 | ------------------------------------- | 1 | Yuri Gagarin | 0 | ------------------------------------- | 2 | Turtle | 0 | ------------------------------------- | 2 | Cheetah | 1 | ---- and so on... (A column- question no. answers refer to, B- answers, C- 0 is false answer, 1 is true) I got 2 macros which should work in order to do the following: | A | B | C | ---------------------------------------------------- question no. | question | | <- font is bold ---------------------------------------------------- | | answer 1 | true/false| ---------------------------------------------------- | | answer 2 | true/false| ---------------------------------------------------- | | answer 3 | true/false| <line break here ---------------------------------------------------- question no. | question | | <- font is bold ---------------------------------------------------- | | answer 1 | true/false| ---------------------------------------------------- | | answer 2 | true/false| ---------------------------------------------------- | | answer 3 | true/false| ..... Macros that I use: 1) Sorting Q&A to QA sheet, questions font in bold Sub macro1() ' ' gsnuxx ' rev 1 ' k = 1 Set q = Sheets("q") Set a = Sheets("a") 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 2) Putting a), b), c), d) in front of the answers and blank row indent. Public Sub ProcessData() Const TEST_COLUMN As String = "B" 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(1).Insert End If Next i End With End Sub The problem is that the macros aren't working as they should - the questions are not bolded properly (the rows are bolded randomly as it seems to me), the blank row indent is not rightly positioned (sometimes it occures in the middle of the answers, instead in the end dividing the question with answers with the following question. I'd like to merge those 2 macros into one which would do all the work. I hope someone can help me with this one ? Thanks in advance ! Marko |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro problem - not working properly
|
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro problem - not working properly
Try this one.
Sub macro1() ' ' gsnuxx ' rev 1 ' k = 1 Set q = Sheets("q") Set a = Sheets("a") Set qa = Sheets("qa") qa.Cells.Clear '<==add this line 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 letter = 97 '<==add this line For j = 1 To na m = a.Cells(j, 1).Value If m = n Then '<==modified the folloing line qa.Cells(k, 2).Value = Chr(letter) & ")" _ & a.Cells(j, 2).Value letter = letter + 1 '<==add this line 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 k = k + 1 '<==add this line Next End Sub Keiji s3cheK wrote: Hey guys, I got 2 sheets in my workbook from one of my web quizzes (both exported from mysql in excel format) sheet Q example: ---------------------------------------------------------------- | A | B | C | -------------------------------------------------- | 1 | Who was the first man on the Moon ? | category 1 | -------------------------------------------------- | 2 | The fastest animal on the planet is.. | category 3 | --- and so on... (A column - question no., B col. - question) sheet A example: ------------------------------------- | A | B | C | ------------------------------------- | 1 | John Glenn | 0 | ------------------------------------- | 1 | Neil Armstrong | 1 | ------------------------------------- | 1 | Yuri Gagarin | 0 | ------------------------------------- | 2 | Turtle | 0 | ------------------------------------- | 2 | Cheetah | 1 | ---- and so on... (A column- question no. answers refer to, B- answers, C- 0 is false answer, 1 is true) I got 2 macros which should work in order to do the following: | A | B | C | ---------------------------------------------------- question no. | question | | <- font is bold ---------------------------------------------------- | | answer 1 | true/false| ---------------------------------------------------- | | answer 2 | true/false| ---------------------------------------------------- | | answer 3 | true/false| <line break here ---------------------------------------------------- question no. | question | | <- font is bold ---------------------------------------------------- | | answer 1 | true/false| ---------------------------------------------------- | | answer 2 | true/false| ---------------------------------------------------- | | answer 3 | true/false| .... Macros that I use: 1) Sorting Q&A to QA sheet, questions font in bold Sub macro1() ' ' gsnuxx ' rev 1 ' k = 1 Set q = Sheets("q") Set a = Sheets("a") 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 2) Putting a), b), c), d) in front of the answers and blank row indent. Public Sub ProcessData() Const TEST_COLUMN As String = "B" 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(1).Insert End If Next i End With End Sub The problem is that the macros aren't working as they should - the questions are not bolded properly (the rows are bolded randomly as it seems to me), the blank row indent is not rightly positioned (sometimes it occures in the middle of the answers, instead in the end dividing the question with answers with the following question. I'd like to merge those 2 macros into one which would do all the work. I hope someone can help me with this one ? Thanks in advance ! Marko |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro problem - not working properly
It works perfecty, tyvm !
"keiji kounoike" <"kounoike AT mbh.nifty.com" wrote in message ... Try this one. Sub macro1() ' ' gsnuxx ' rev 1 ' k = 1 Set q = Sheets("q") Set a = Sheets("a") Set qa = Sheets("qa") qa.Cells.Clear '<==add this line 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 letter = 97 '<==add this line For j = 1 To na m = a.Cells(j, 1).Value If m = n Then '<==modified the folloing line qa.Cells(k, 2).Value = Chr(letter) & ")" _ & a.Cells(j, 2).Value letter = letter + 1 '<==add this line 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 k = k + 1 '<==add this line Next End Sub Keiji s3cheK wrote: Hey guys, I got 2 sheets in my workbook from one of my web quizzes (both exported from mysql in excel format) sheet Q example: ---------------------------------------------------------------- | A | B | C | -------------------------------------------------- | 1 | Who was the first man on the Moon ? | category 1 | -------------------------------------------------- | 2 | The fastest animal on the planet is.. | category 3 | --- and so on... (A column - question no., B col. - question) sheet A example: ------------------------------------- | A | B | C | ------------------------------------- | 1 | John Glenn | 0 | ------------------------------------- | 1 | Neil Armstrong | 1 | ------------------------------------- | 1 | Yuri Gagarin | 0 | ------------------------------------- | 2 | Turtle | 0 | ------------------------------------- | 2 | Cheetah | 1 | ---- and so on... (A column- question no. answers refer to, B- answers, C- 0 is false answer, 1 is true) I got 2 macros which should work in order to do the following: | A | B | C | ---------------------------------------------------- question no. | question | | <- font is bold ---------------------------------------------------- | | answer 1 | true/false| ---------------------------------------------------- | | answer 2 | true/false| ---------------------------------------------------- | | answer 3 | true/false| <line break here ---------------------------------------------------- question no. | question | | <- font is bold ---------------------------------------------------- | | answer 1 | true/false| ---------------------------------------------------- | | answer 2 | true/false| ---------------------------------------------------- | | answer 3 | true/false| .... Macros that I use: 1) Sorting Q&A to QA sheet, questions font in bold Sub macro1() ' ' gsnuxx ' rev 1 ' k = 1 Set q = Sheets("q") Set a = Sheets("a") 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 2) Putting a), b), c), d) in front of the answers and blank row indent. Public Sub ProcessData() Const TEST_COLUMN As String = "B" 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(1).Insert End If Next i End With End Sub The problem is that the macros aren't working as they should - the questions are not bolded properly (the rows are bolded randomly as it seems to me), the blank row indent is not rightly positioned (sometimes it occures in the middle of the answers, instead in the end dividing the question with answers with the following question. I'd like to merge those 2 macros into one which would do all the work. I hope someone can help me with this one ? Thanks in advance ! Marko |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro problem - not working properly
Thanks for letting me know, though i don't know what tyvm means.
Regards Keiji s3cheK wrote: It works perfecty, tyvm ! "keiji kounoike" <"kounoike AT mbh.nifty.com" wrote in message ... Try this one. Sub macro1() ' ' gsnuxx ' rev 1 ' k = 1 Set q = Sheets("q") Set a = Sheets("a") Set qa = Sheets("qa") qa.Cells.Clear '<==add this line 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 letter = 97 '<==add this line For j = 1 To na m = a.Cells(j, 1).Value If m = n Then '<==modified the folloing line qa.Cells(k, 2).Value = Chr(letter) & ")" _ & a.Cells(j, 2).Value letter = letter + 1 '<==add this line 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 k = k + 1 '<==add this line Next End Sub Keiji s3cheK wrote: Hey guys, I got 2 sheets in my workbook from one of my web quizzes (both exported from mysql in excel format) sheet Q example: ---------------------------------------------------------------- | A | B | C | -------------------------------------------------- | 1 | Who was the first man on the Moon ? | category 1 | -------------------------------------------------- | 2 | The fastest animal on the planet is.. | category 3 | --- and so on... (A column - question no., B col. - question) sheet A example: ------------------------------------- | A | B | C | ------------------------------------- | 1 | John Glenn | 0 | ------------------------------------- | 1 | Neil Armstrong | 1 | ------------------------------------- | 1 | Yuri Gagarin | 0 | ------------------------------------- | 2 | Turtle | 0 | ------------------------------------- | 2 | Cheetah | 1 | ---- and so on... (A column- question no. answers refer to, B- answers, C- 0 is false answer, 1 is true) I got 2 macros which should work in order to do the following: | A | B | C | ---------------------------------------------------- question no. | question | | <- font is bold ---------------------------------------------------- | | answer 1 | true/false| ---------------------------------------------------- | | answer 2 | true/false| ---------------------------------------------------- | | answer 3 | true/false| <line break here ---------------------------------------------------- question no. | question | | <- font is bold ---------------------------------------------------- | | answer 1 | true/false| ---------------------------------------------------- | | answer 2 | true/false| ---------------------------------------------------- | | answer 3 | true/false| .... Macros that I use: 1) Sorting Q&A to QA sheet, questions font in bold Sub macro1() ' ' gsnuxx ' rev 1 ' k = 1 Set q = Sheets("q") Set a = Sheets("a") 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 2) Putting a), b), c), d) in front of the answers and blank row indent. Public Sub ProcessData() Const TEST_COLUMN As String = "B" 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(1).Insert End If Next i End With End Sub The problem is that the macros aren't working as they should - the questions are not bolded properly (the rows are bolded randomly as it seems to me), the blank row indent is not rightly positioned (sometimes it occures in the middle of the answers, instead in the end dividing the question with answers with the following question. I'd like to merge those 2 macros into one which would do all the work. I hope someone can help me with this one ? Thanks in advance ! Marko |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro problem - not working properly
|
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro problem - not working properly
Thanks, I'm poor at English, so i can learn a lot not only about Excel
but also about English here. Regards Keiji Nigel wrote: tyvm = Thank you very much! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro not working properly when run automatically | Excel Discussion (Misc queries) | |||
| Macro is not working properly | Excel Discussion (Misc queries) | |||
| macro problem - not working properly | Excel Programming | |||
| Macro Not Working Properly | Excel Programming | |||
| Macro "1 tall/1 wide" not working properly | Excel Worksheet Functions | |||
Linear Mode
