Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Generate Payment Statement
I'm hoping I can do this with functions but it may need to be a macro. I've been looking at it for awhile now and can't work it out. I have two columns. Column A has the dates of each day, Column B has a figure next to the date that an amount was paid. Some days have nothing in column B. A.........B 1/1/05 10 2/1/05 3/1/05 5 4/1/05 5 5/1/05 I have to produce a statement of payments received. Therefore I want a listing of the date with the amount received to be generated automatically. from above: 1/1/05 10 3/1/05 5 4/1/05 5 Any help would be very much appreciated. thanks adrian -- myty ------------------------------------------------------------------------ myty's Profile: http://www.excelforum.com/member.php...o&userid=24592 View this thread: http://www.excelforum.com/showthread...hreadid=381844 |
#2
|
|||
|
|||
select row 1 go to, data-auto filter go to payment, drop down box select "non blanks" -- anilsolipuram ------------------------------------------------------------------------ anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271 View this thread: http://www.excelforum.com/showthread...hreadid=381844 |
#3
|
|||
|
|||
Thanks for the reply. This method still involves copying and pasting to other cells. I was hoping for a solution that filled the other cells automatically. -- myty ------------------------------------------------------------------------ myty's Profile: http://www.excelforum.com/member.php...o&userid=24592 View this thread: http://www.excelforum.com/showthread...hreadid=381844 |
#4
|
|||
|
|||
backup you workbook before trying this macro This will copy cells with payment values in column e Sub macro() Dim i, c, c1 As Variant i = 0 c = 2 c1 = 1 While i = 0 If (Range("a" & c).Value = "") Then i = 1 Else If Range("b" & c).Value = "" Then Else If (c1 = 1) Then Range("a1:b1").Select Selection.Copy Range("e" & c1).Select ActiveSheet.Paste c1 = c1 + 1 End If Range("a" & c & ":" & "b" & c).Select Selection.Copy Range("e" & c1).Select ActiveSheet.Paste c1 = c1 + 1 End If End If c = c + 1 Wend End Sub -- anilsolipuram ------------------------------------------------------------------------ anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271 View this thread: http://www.excelforum.com/showthread...hreadid=381844 |
#5
|
|||
|
|||
Back up your workbook before trying this macro simpler version of macro, using auto filter, it will copy to column e Sub Macro10() Rows("1:1").Select Selection.AutoFilter Range("B1").Select Selection.AutoFilter Field:=2, Criteria1:="<" Range("A1").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Range("e").Select ActiveSheet.Paste Application.CutCopyMode = False Selection.AutoFilter End Sub -- anilsolipuram ------------------------------------------------------------------------ anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271 View this thread: http://www.excelforum.com/showthread...hreadid=381844 |
#6
|
|||
|
|||
Great, thanks for that. I'll play with it a bit but should do the trick. -- myty ------------------------------------------------------------------------ myty's Profile: http://www.excelforum.com/member.php...o&userid=24592 View this thread: http://www.excelforum.com/showthread...hreadid=381844 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loan Schedule with Balloon Payment | Excel Worksheet Functions | |||
Do I need a sumif or sum of a vlookup formula? | Excel Worksheet Functions | |||
Trying to Manipulate a Mortgage Payment (HELP!) | Excel Worksheet Functions | |||
how do you get a positive number payment with a mortgage payment . | Excel Worksheet Functions | |||
Statement | Excel Worksheet Functions |