For Next If help

[Rayashe]

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

 

[Roger Govier]

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

 

[Rayashe]

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.

 

[Rayashe]

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