Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple For Next Statement, Excel 2000 & 2003
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple For Next Statement, Excel 2000 & 2003
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 Tom Ogilvy wrote: Sub aAA() For Each BN In Range("A1:A5") i = BN.Row TBC = BN.Offset(0, 1) j = 1 For v = 1 To TBC Cells(v, i + 2).Value = _ BN & " " & j & "-" & j + 1 j = j + 2 Next Next End Sub that would fill the number of cells specified in column B. if you mean the last number in Lex 1-3 should stop at the value next to lex, then you would do For v = 1 To TBC/2 but the last number is always even -- Regards, Tom Ogilvy "jfcby" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple For Next Statement, Excel 2000 & 2003
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 wrote: My inattention Sub aAA() For Each BN In Range("A1:A5") i = BN.Row TBC = BN.Offset(0, 1) j = 1 For v = 1 To TBC Cells(v, i + 2).Value = _ BN & " " & j & "-" & j + 1 j = j + 3 Next Next End Sub or For v = 1 To TBC/3 -- Regards, Tom Ogilvy "jfcby" wrote: 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 Tom Ogilvy wrote: Sub aAA() For Each BN In Range("A1:A5") i = BN.Row TBC = BN.Offset(0, 1) j = 1 For v = 1 To TBC Cells(v, i + 2).Value = _ BN & " " & j & "-" & j + 1 j = j + 2 Next Next End Sub that would fill the number of cells specified in column B. if you mean the last number in Lex 1-3 should stop at the value next to lex, then you would do For v = 1 To TBC/2 but the last number is always even -- Regards, Tom Ogilvy "jfcby" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple For Next Statement, Excel 2000 & 2003
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" wrote in message ups.com... 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 wrote: My inattention Sub aAA() For Each BN In Range("A1:A5") i = BN.Row TBC = BN.Offset(0, 1) j = 1 For v = 1 To TBC Cells(v, i + 2).Value = _ BN & " " & j & "-" & j + 1 j = j + 3 Next Next End Sub or For v = 1 To TBC/3 -- Regards, Tom Ogilvy "jfcby" wrote: 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 Tom Ogilvy wrote: Sub aAA() For Each BN In Range("A1:A5") i = BN.Row TBC = BN.Offset(0, 1) j = 1 For v = 1 To TBC Cells(v, i + 2).Value = _ BN & " " & j & "-" & j + 1 j = j + 2 Next Next End Sub that would fill the number of cells specified in column B. if you mean the last number in Lex 1-3 should stop at the value next to lex, then you would do For v = 1 To TBC/2 but the last number is always even -- Regards, Tom Ogilvy "jfcby" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple For Next Statement, Excel 2000 & 2003
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 wrote: 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" wrote in message ups.com... 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 wrote: My inattention Sub aAA() For Each BN In Range("A1:A5") i = BN.Row TBC = BN.Offset(0, 1) j = 1 For v = 1 To TBC Cells(v, i + 2).Value = _ BN & " " & j & "-" & j + 1 j = j + 3 Next Next End Sub or For v = 1 To TBC/3 -- Regards, Tom Ogilvy "jfcby" wrote: 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 Tom Ogilvy wrote: Sub aAA() For Each BN In Range("A1:A5") i = BN.Row TBC = BN.Offset(0, 1) j = 1 For v = 1 To TBC Cells(v, i + 2).Value = _ BN & " " & j & "-" & j + 1 j = j + 2 Next Next End Sub that would fill the number of cells specified in column B. if you mean the last number in Lex 1-3 should stop at the value next to lex, then you would do For v = 1 To TBC/2 but the last number is always even -- Regards, Tom Ogilvy "jfcby" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple For Next Statement, Excel 2000 & 2003
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 -- Regards, Tom Ogilvy "jfcby" wrote in message oups.com... 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 wrote: 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" wrote in message ups.com... 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 wrote: My inattention Sub aAA() For Each BN In Range("A1:A5") i = BN.Row TBC = BN.Offset(0, 1) j = 1 For v = 1 To TBC Cells(v, i + 2).Value = _ BN & " " & j & "-" & j + 1 j = j + 3 Next Next End Sub or For v = 1 To TBC/3 -- Regards, Tom Ogilvy "jfcby" wrote: 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 Tom Ogilvy wrote: Sub aAA() For Each BN In Range("A1:A5") i = BN.Row TBC = BN.Offset(0, 1) j = 1 For v = 1 To TBC Cells(v, i + 2).Value = _ BN & " " & j & "-" & j + 1 j = j + 2 Next Next End Sub that would fill the number of cells specified in column B. if you mean the last number in Lex 1-3 should stop at the value next to lex, then you would do For v = 1 To TBC/2 but the last number is always even -- Regards, Tom Ogilvy "jfcby" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple For Next Statement, Excel 2000 & 2003
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 Tom Ogilvy wrote: 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 -- Regards, Tom Ogilvy "jfcby" wrote in message oups.com... 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 wrote: 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" wrote in message ups.com... 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 wrote: My inattention Sub aAA() For Each BN In Range("A1:A5") i = BN.Row TBC = BN.Offset(0, 1) j = 1 For v = 1 To TBC Cells(v, i + 2).Value = _ BN & " " & j & "-" & j + 1 j = j + 3 Next Next End Sub or For v = 1 To TBC/3 -- Regards, Tom Ogilvy "jfcby" wrote: 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 Tom Ogilvy wrote: Sub aAA() For Each BN In Range("A1:A5") i = BN.Row TBC = BN.Offset(0, 1) j = 1 For v = 1 To TBC Cells(v, i + 2).Value = _ BN & " " & j & "-" & j + 1 j = j + 2 Next Next End Sub that would fill the number of cells specified in column B. if you mean the last number in Lex 1-3 should stop at the value next to lex, then you would do For v = 1 To TBC/2 but the last number is always even -- Regards, Tom Ogilvy "jfcby" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple For Next Statement, Excel 2000 & 2003
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 wrote: 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 Tom Ogilvy wrote: 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 -- Regards, Tom Ogilvy "jfcby" wrote in message oups.com... 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 wrote: 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" wrote in message ups.com... 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 wrote: My inattention Sub aAA() For Each BN In Range("A1:A5") i = BN.Row TBC = BN.Offset(0, 1) j = 1 For v = 1 To TBC Cells(v, i + 2).Value = _ BN & " " & j & "-" & j + 1 j = j + 3 Next Next End Sub or For v = 1 To TBC/3 -- Regards, Tom Ogilvy "jfcby" wrote: 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 Tom Ogilvy wrote: Sub aAA() For Each BN In Range("A1:A5") i = BN.Row TBC = BN.Offset(0, 1) j = 1 For v = 1 To TBC Cells(v, i + 2).Value = _ BN & " " & j & "-" & j + 1 j = j + 2 Next Next End Sub that would fill the number of cells specified in column B. if you mean the last number in Lex 1-3 should stop at the value next to lex, then you would do For v = 1 To TBC/2 but the last number is always even -- Regards, Tom Ogilvy "jfcby" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple For Next Statement, Excel 2000 & 2003
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 Tom Ogilvy wrote: 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 -- Regards, Tom Ogilvy "jfcby" wrote in message oups.com... 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 wrote: 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" wrote in message ups.com... 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 wrote: My inattention Sub aAA() For Each BN In Range("A1:A5") i = BN.Row TBC = BN.Offset(0, 1) j = 1 For v = 1 To TBC Cells(v, i + 2).Value = _ BN & " " & j & "-" & j + 1 j = j + 3 Next Next End Sub or For v = 1 To TBC/3 -- Regards, Tom Ogilvy "jfcby" wrote: 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 Tom Ogilvy wrote: Sub aAA() For Each BN In Range("A1:A5") i = BN.Row TBC = BN.Offset(0, 1) j = 1 For v = 1 To TBC Cells(v, i + 2).Value = _ BN & " " & j & "-" & j + 1 j = j + 2 Next Next End Sub that would fill the number of cells specified in column B. if you mean the last number in Lex 1-3 should stop at the value next to lex, then you would do For v = 1 To TBC/2 but the last number is always even -- Regards, Tom Ogilvy "jfcby" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
EXcel 2000 IF-AND Statement | Excel Discussion (Misc queries) | |||
Multiple calculations within cell? Writing in Excel 2000, using inExcel 2003 | Excel Worksheet Functions | |||
Calendar change month from multiple sheets to one sheet, Excel 2000 & 2003 | Excel Programming | |||
If statement combine, Excel 2000 & 2003 | Excel Programming | |||
How do you run a Macro from an IF statement in Excel 2000? | Excel Worksheet Functions |