For Next If help

R

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
 
R

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
 
R

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.
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top