ExcelBanter - Macro problem - not working properly

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Macro problem - not working properly (https://www.excelbanter.com/excel-programming/427249-macro-problem-not-working-properly.html)

s3cheK

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

Don Guillett

Macro problem - not working properly

keiji kounoike

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

s3cheK

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

keiji kounoike

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

Nigel[_2_]

Macro problem - not working properly

keiji kounoike

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!

All times are GMT +1. The time now is 12:07 AM.