Multiple For Next Statement, Excel 2000 & 2003

[jfcby]

Hello,

In my worksheet my data looks like this:

Column A Column B
Gen 50
Ex 31
Lev 44
Num 25
Deu 36

In ColumnA and ColumnB my data goes down 66 rows. I'm tring to put my
data beginning in ColumnC after finding next blank cell then move over
to next column like this:

ColumnC ColumnD ColumnE
ColumnF
Gen 1-3 Ex 1-3 Lev 1-3
Num 1-3
Gen 4-6 Ex 4-6 Lev 4-6
Num 4-6
Gen 7-9 Ex 7-9 Lev 7-9
Num 7-9
Gen 10-12 Ex 10-12 Lev 10-12
Num 10-12
until 50 is reached until 31 is reached until 44 is reached
until 25 is reached

Here is the code I have so far but it is not working correctly.

Sub aBibleRead()

Dim BN As Range, TBC As Range
Dim v As Long, v2 As Long, v3 As Long, v4 As Long

For Each BN In Range("A1:A6")
For Each TBC In Range("B1:B6")
For v = 1 To TBC
For v2 = 3 To TBC
For v3 = v + 3 To TBC
For v4 = v2 + 3 To TBC
Range("E65536").End(xlUp).Offset(1, 0).Select
ActiveCell.Value = BN & " " & v3 & "-" & v4
Next v4
Next v3
Next v2
Next v
Next TBC
Next BN

End Sub

Thank you for your help,
jfcby

 

[jfcby]

Hello Tom,

Thank you for your response but I need the numbers for ex:
Gen 1-3
Gen 4-6
Gen 7-9

but I'm getting:
Gen 1-2
Gen 3-4
Gen 5-6

I've changed some numbers in your code but it does not work right.

Thank you for your help,
jfcby

 

[jfcby]

Thank you Tom for your help!

I have another question is there a way for the code to be changed so
that the last cell with data would equal the number in Column B
example:

Column B Last Cell With Data would be
50 Gen 47-50 or Ex 19-20 or 5-7
20
7
3
5


Thank you for your help,
jfcby

 

[Tom Ogilvy]

I was hasty with my second posting, but this should do both requests

Sub aAA()
Dim i As Long, BN As Range
Dim TBC As Long, bExitfor As Boolean
Dim j As Long, v As Long
Dim k As Long
For Each BN In Range("A1:A5")
i = BN.Row
TBC = BN.Offset(0, 1)
j = 1
bExitfor = False
For v = 1 To TBC / 2
k = j + 2
If k > TBC Or k + 1 = TBC Then
k = TBC
bExitfor = True
End If
Cells(v, i + 2).Value = _
BN & " " & j & "-" & k
If bExitfor Then Exit For
j = j + 3
Next
Next

End Sub

--
Regards,
Tom Ogilvy

 

[jfcby]

Hello Tom,

When I tried your code it adds extra numbers at the end of several of
the columns like the below example also I adjusted the code to use 1-4,
5-8 & 9-12 but I 'm not sure if I did it right:

Column 1 Column 2 Column 3
Ex 37-40 Nu 33-36 Ru 1-4
Ex 41-40 Nu 37-36 Ru 5-4

Modifed Code for numbering format 1-4, 5-8, 9-12

Sub aAA()
Dim i As Long, BN As Range
Dim TBC As Long, bExitfor As Boolean
Dim j As Long, v As Long
Dim k As Long
For Each BN In Range("A:A")
i = BN.Row
TBC = BN.Offset(0, 1)
j = 1
bExitfor = False
For v = 1 To TBC / 2
k = j + 3
If k > TBC Or k + 1 = TBC Then
k = TBC
bExitfor = True
End If
Cells(v, i + 2).Value = _
BN & " " & j & "-" & k
If bExitfor Then Exit For
j = j + 4
Next
Next
End Sub

Thank you for your help,
jfcby

 

[Tom Ogilvy]

Sub AAB()
Dim i As Long, BN As Range
Dim TBC As Long, bExitfor As Boolean
Dim j As Long, v As Long
Dim k As Long, ii As Long
Dim ub As Long
Dim lStep As Long
lStep = 4
j = 0
For Each BN In Range(Cells(1, 1), Cells(1, 1).End(xlDown))
i = BN.Row
TBC = BN.Offset(0, 1)
bExitfor = False
ub = (TBC \ lStep) * lStep
ii = 0
j = j + 1
For v = 1 To ub Step lStep
ii = ii + 1
If v + (lStep - 1) = ub Then
k = TBC
Else
k = v + (lStep - 1)
End If
Cells(ii, j + 2).Value = _
BN & " " & v & "-" & k
Next
Next
End Sub

 

[jfcby]

Hello Tom,

When I tried your code it works great but one more request. When my
data in columnB is 1 or 2 or 3 the code will leave the column blank.
Can the code be changed so that the columns will have this data
included also?

Thank you for your help,
jfcby

 

[jfcby]

Hello Tom,

More details added to the previous post.

When my data in columnB is number 1, 2, or 3 the column selected to add
data is left blank example below:

ColumnA ColumnB
Gen 20
Ex 3
Num 1
Ha 50
Hb 40

When columnB has a 1, 2, or 3 the selected column to add data is left
blank.

I would like for the selected column to format like this:

Selected column to add data:
Ex 1-3
Num 1

Thank you for your help'
jfcby

 

[jfcby]

Hello Tom,

Your code has saved me time. I've been diligently studying your code to
modify it according to my previous post. But I still do not understand
it to make those changes. So I used some of your code that I did learn
to make a macro that did work and added it below your code and it works
great.

Sub AAB()
'works but does not add books if there is only one chapter
Dim i As Long, BN As Range, TBC2 As Range
Dim TBC As Long, bExitfor As Boolean
Dim j As Long, v As Long
Dim k As Long, ii As Long
Dim ub As Long
Dim lStep As Long
lStep = 4
j = 0
For Each BN In Range(Cells(1, 1), Cells(1, 1).End(xlDown))
i = BN.Row
TBC = BN.Offset(0, 1)
bExitfor = False
ub = (TBC \ lStep) * lStep
ii = 0
j = j + 1
For v = 1 To ub Step lStep
ii = ii + 1
If v + (lStep - 1) = ub Then
k = TBC
Else
k = v + (lStep - 1)
End If
Cells(ii, j + 2).Value = _
BN & " " & v & "-" & k
Next
Next

'adds books if there is only one chapter
For Each TBC2 In Range("B:B")
If TBC2 = "1" Then
Range("B:B").End(xlToRight).Offset(0, 1) = _
TBC2.Offset(0, -1).Value & " " & TBC2.Value
ElseIf TBC2 = "2" Then
Range("B:B").End(xlToRight).Offset(0, 1) = _
TBC2.Offset(0, -1).Value & " " & TBC2.Value - 1 _
& "-" & TBC2.Value
ElseIf TBC2 = "3" Then
Range("B:B").End(xlToRight).Offset(0, 1) = _
TBC2.Offset(0, -1).Value & " " & TBC2.Value - 2 _
& "-" & TBC2.Value
End If
Next
End Sub

I will continue to study your code to understand it better because it
will help me with other macros I'll need to create in the future.

Thank you for all your help,
jfcby