Macro to get Amount and Date

[Rayashe]

Columns A to G contain client data such as name etc.
Columns J to CS contain payments.
Row 3 in each of the above columns contain the payment date.
Payments are in Rows 5 to 674

In Column H I need to put in the first payment, and Column I needs to have
the date of that first payment.

How can I do this?

Thanks.

 

[JLGWhiz]

If your question is how to fill columns H and I via code, then you would have
to furnish some information about where to find the data to put in those
columns, or how it can be calculated from the data in row 3 and payments
entered in the other rows. Otherwise, I would say to just type it in as you
go since there is no way for us to know what those first payment dates and
amounts are from the original posting.

 

[Rayashe]

J5 to CS674 contains the data that needs to go in Column H and J3 to CS674
contains the date that would need to go into column I so that columns H & I
show how much the first payment was and what date it was made.

 

[JLGWhiz]

Would the entry in column J not be the first payment for each account? Or
are entries made randomly, or are payments made randomly? I am trying to
visualize why you are needing code to do this task. It seems that it could
be done with a cut an paste or copy and paste action.

The additional information did not help to understand the problem.

 

[Rayashe]

No. Column J represents the start of the tax year (06 Apr 2007) and does
contain some opening balances, which would be easy enough to do a data sort
and copy/paste, but it is the remainder who have no opening balance and whose
first payment is random. For a report that I have set up a Mail-Merge, it
needs to know what their opening balance was at the start of the tax year, or
if there is no opening balance, then what was the value of their first
payment and what date was it made.
I could go through each column and do various data sorts and copy/paste, but
with 671 individuals it could take more time than I'd like.

 

[JLGWhiz]

Well, If I have gleaned the information correctly about your sheet layout and
what you are trying to do, this should work:

Sub date_amt()
Dim lastRow As Long, fCol As Long, i As Long
lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For i = 5 To lastRow
If Cells(i, 10) > "" Then
Cells(i, 10).Copy Cells(i, 9)
Cells(3, 10).Copy Cells(i, 8)
Else
fCol = Cells(i, 8).End(xlToRight).Column
Cells(i, fCol).Copy Cells(i, 9)
Cells(3, fCol).Copy Cells(i, 8)
End If
Next
End Sub

It checks each row starting at row 5. If there is an entry in column J on
the row being evaluated then it will copy that data to column I and then copy
the date from row three of that column to column H. If the cell in column J
is blank, then it will find the first cell with data and copy that to column
I, then copy the date from row three or the same column to column H of the
row being evaluated. TEST THIS ON A COPY BEFORE INSTALLING FOR PERMANENT USE.

 

[Rayashe]

Excellent! Thanks JLGWhiz. Worked as required, I just had to swap the 8 and 9
around in the code and removed lastrow and made i = 5 to 674.
Thanks for the help.