Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Next If help
Hi,
I have a spreadsheet containing client details in columns 1 to 9, payments in columns 12 to 116, and interest on those payments in columns 119 to 223. Row 3 of columns 12 to 116 contains the date of payments. The data runs from rows 4 to 207. I have copied the details in columns 1 to 9 and rows 4 to 207 down to start from row 220, now against each client name I need to show Payment, Interest, Date, Payment, Interest, Date, Payment, Interest, Date, etc so that I can use the information on a mailmerge document. The problem is that not each column in the 12 to 116 range has a payment. Some clients may have as many as 12 payments, and some may have as few as 1 payment. The code I have tried (which does not work) is the following: Sub date_amt() Dim i As Long, j As Long For i = 4 To 207 For j = 12 To 116 If Cells(i, j) = "" Then Next j Else Cells(i, j).Copy Cells(i + 216, 10).End(xlToRight + 1).Column Cells(i, j + 107).Copy Cells(i + 216, 10).End(xlToRight + 1).Column Cells(3, j).Copy Cells(i + 216, 10).End(xlToRight + 1).Column Next j End If Next i End Sub It is getting stuck at the first Next statement with a Compile Error Next without For. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Next If help
Hi
you can't have 2 next within the same For loop For i = 4 To 207 For j = 12 To 116 If Cells(i, j) = "" Then Cells(i, j).Copy Cells(i + 216, 10).End(xlToRight + 1).Column Cells(i, j + 107).Copy Cells(i + 216, 10).End(xlToRight + 1).Column Cells(3, j).Copy Cells(i + 216, 10).End(xlToRight + 1).Column End If Next j Next i End Sub -- Regards Roger Govier "Rayashe" wrote in message ... Hi, I have a spreadsheet containing client details in columns 1 to 9, payments in columns 12 to 116, and interest on those payments in columns 119 to 223. Row 3 of columns 12 to 116 contains the date of payments. The data runs from rows 4 to 207. I have copied the details in columns 1 to 9 and rows 4 to 207 down to start from row 220, now against each client name I need to show Payment, Interest, Date, Payment, Interest, Date, Payment, Interest, Date, etc so that I can use the information on a mailmerge document. The problem is that not each column in the 12 to 116 range has a payment. Some clients may have as many as 12 payments, and some may have as few as 1 payment. The code I have tried (which does not work) is the following: Sub date_amt() Dim i As Long, j As Long For i = 4 To 207 For j = 12 To 116 If Cells(i, j) = "" Then Next j Else Cells(i, j).Copy Cells(i + 216, 10).End(xlToRight + 1).Column Cells(i, j + 107).Copy Cells(i + 216, 10).End(xlToRight + 1).Column Cells(3, j).Copy Cells(i + 216, 10).End(xlToRight + 1).Column Next j End If Next i End Sub It is getting stuck at the first Next statement with a Compile Error Next without For. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Next If help
Hi,
OK, I have changed it to: Sub date_amt() Dim i As Long, j As Long For i = 4 To 207 For j = 12 To 116 If Cells(i, j) "" Then Cells(i, j).Copy Cells(i + 216, 10).End(xlToRight + 1).Column Cells(i, j + 107).Copy Cells(i + 216, 10).End(xlToRight + 1).Column Cells(3, j).Copy Cells(i + 216, 10).End(xlToRight + 1).Column End If Next j Next i End Sub But now I get a Run-time error '1004' Application defined or object defined error - and it highlights Cells(i, j).Copy Cells(i + 216, 10).End(xlToRight + 1).Column I changed the = "" to "" because if there is nothing in cells(4,12) then it must move along until it finds something and then copy the payment amount, interest amount and date of payment 216 rows further down. "Roger Govier" wrote: Hi you can't have 2 next within the same For loop For i = 4 To 207 For j = 12 To 116 If Cells(i, j) = "" Then Cells(i, j).Copy Cells(i + 216, 10).End(xlToRight + 1).Column Cells(i, j + 107).Copy Cells(i + 216, 10).End(xlToRight + 1).Column Cells(3, j).Copy Cells(i + 216, 10).End(xlToRight + 1).Column End If Next j Next i End Sub -- Regards Roger Govier "Rayashe" wrote in message ... Hi, I have a spreadsheet containing client details in columns 1 to 9, payments in columns 12 to 116, and interest on those payments in columns 119 to 223. Row 3 of columns 12 to 116 contains the date of payments. The data runs from rows 4 to 207. I have copied the details in columns 1 to 9 and rows 4 to 207 down to start from row 220, now against each client name I need to show Payment, Interest, Date, Payment, Interest, Date, Payment, Interest, Date, etc so that I can use the information on a mailmerge document. The problem is that not each column in the 12 to 116 range has a payment. Some clients may have as many as 12 payments, and some may have as few as 1 payment. The code I have tried (which does not work) is the following: Sub date_amt() Dim i As Long, j As Long For i = 4 To 207 For j = 12 To 116 If Cells(i, j) = "" Then Next j Else Cells(i, j).Copy Cells(i + 216, 10).End(xlToRight + 1).Column Cells(i, j + 107).Copy Cells(i + 216, 10).End(xlToRight + 1).Column Cells(3, j).Copy Cells(i + 216, 10).End(xlToRight + 1).Column Next j End If Next i End Sub It is getting stuck at the first Next statement with a Compile Error Next without For. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Next If help
No worries - found the solution:
Sub date_amt() Dim i As Long, j As Long For i = 4 To 207 For j = 12 To 116 If Cells(i, j) 0 Then Cells(i, j).Copy Cells(i + 216, 10).End(xlToRight).Offset(0, 1) Cells(i, j + 107).Copy Cells(i + 216, 10).End(xlToRight).Offset(0, 1) Cells(3, j).Copy Cells(i + 216, 10).End(xlToRight).Offset(0, 1) End If Next j Next i End Sub Thanks for help "Rayashe" wrote: Hi, OK, I have changed it to: Sub date_amt() Dim i As Long, j As Long For i = 4 To 207 For j = 12 To 116 If Cells(i, j) "" Then Cells(i, j).Copy Cells(i + 216, 10).End(xlToRight + 1).Column Cells(i, j + 107).Copy Cells(i + 216, 10).End(xlToRight + 1).Column Cells(3, j).Copy Cells(i + 216, 10).End(xlToRight + 1).Column End If Next j Next i End Sub But now I get a Run-time error '1004' Application defined or object defined error - and it highlights Cells(i, j).Copy Cells(i + 216, 10).End(xlToRight + 1).Column I changed the = "" to "" because if there is nothing in cells(4,12) then it must move along until it finds something and then copy the payment amount, interest amount and date of payment 216 rows further down. "Roger Govier" wrote: Hi you can't have 2 next within the same For loop For i = 4 To 207 For j = 12 To 116 If Cells(i, j) = "" Then Cells(i, j).Copy Cells(i + 216, 10).End(xlToRight + 1).Column Cells(i, j + 107).Copy Cells(i + 216, 10).End(xlToRight + 1).Column Cells(3, j).Copy Cells(i + 216, 10).End(xlToRight + 1).Column End If Next j Next i End Sub -- Regards Roger Govier "Rayashe" wrote in message ... Hi, I have a spreadsheet containing client details in columns 1 to 9, payments in columns 12 to 116, and interest on those payments in columns 119 to 223. Row 3 of columns 12 to 116 contains the date of payments. The data runs from rows 4 to 207. I have copied the details in columns 1 to 9 and rows 4 to 207 down to start from row 220, now against each client name I need to show Payment, Interest, Date, Payment, Interest, Date, Payment, Interest, Date, etc so that I can use the information on a mailmerge document. The problem is that not each column in the 12 to 116 range has a payment. Some clients may have as many as 12 payments, and some may have as few as 1 payment. The code I have tried (which does not work) is the following: Sub date_amt() Dim i As Long, j As Long For i = 4 To 207 For j = 12 To 116 If Cells(i, j) = "" Then Next j Else Cells(i, j).Copy Cells(i + 216, 10).End(xlToRight + 1).Column Cells(i, j + 107).Copy Cells(i + 216, 10).End(xlToRight + 1).Column Cells(3, j).Copy Cells(i + 216, 10).End(xlToRight + 1).Column Next j End If Next i End Sub It is getting stuck at the first Next statement with a Compile Error Next without For. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|