IF Statement in VBA with Offset

[DavG63]

Hi

I hope someone is able to help me with this. I've posted the same question on a couple of other forums but had no luck with a solution (I'm not apparently allowed to post links to these until I've posted more on the forum).

I have an Excel conversion of a bank statement which I would like to reorganise to remove unnecessary rows. The five columns are Date, Type, Description, Debit and Credit. Post-conversion some of the transactions run over several rows, ie.

Column A Column B Column C Column D Column E
25/11/2015 Transaction Name
Transaction Name
Transaction Name 10.00

I'm trying to write code that looks for the all the instances of a blank cell in the Date field (Column A), which have a value in either Debit (Column D) or Credit (Column E).

Once it finds an instance that matches, the code needs to move the values that are in either Column D or Column E to the nearest row in Column A above it that has a value (i.e. the transaction date) and where Columns D and E are blank (more for error checking than necessity).

Any help that anyone is willing to offer would be gratefully received.

Thanks in anticipation.

Dav

[SamT]

what sould this look like when done


Date
Type
Description
Debit
Credit


x


d







c





d



x










c







c





d





I made that nifty table in Excel, then copied and pasted into this post. Then I went "Go Advanced" and formatted the table.

[DavG63]

Hi

Sorry I probably haven't explained it as well as I probably could. I didn't realise I could put in a grid as an alternative.




A
B
C
D
E
F


1
Date
Type
Description
Debit
Credit
Balance


2
25/11/2015
Credit Card
Mastercard





3


Transaction 0010020





4


0504050-185
1000.00




5
25/11/2015
DD
Vodafone
50.00




6
25/11/2015
Credit
Transfer from





7


60-04-00

1050.00













This is an example of what I have at present. What I'm looking to achieve, is to design a macro which will search Column A to find blank cells. It then needs to check those rows to see whether there are any values in the same row in either Column D or E.

Taking the transaction in A2 as an example, I'd like the macro to search and to be able to identify A3 & A4 as meeting the initial criteria of being blank. It then should check D3 & E3 and D4 & E4 to see if they contain values. After finding a value in D4, the macro should take that value and move it into D2 so that it's in line with the rest of the transaction.

Then once that's done rows 3 and 4 should delete themselves and the macro should move on down the column, skipping past A5 as everything is in line and stopping at A7 etc.

I hope this makes more sense than my initial post.

Thanks for any assistance.

Dav

[SamT]

Yes it does. An Image is worth a thousand words. :)

Can we make some assumptions? If we know the last used Row, which will be the last row in Descriptions that has an entry. That only the Last empty Row in Date or Type below any entry therein has any significance?

In the grid you gave, the last used Row is 7, therefore only the Debits/Credits in Rows 4 and 7 have significance.

[DavG63]

Yes that's right. Only the entries in D4 and E7 would be of any interest to the user.

[SamT]

This goes in the Code page of the affected Worksheet
Option Explicit

Sub CollapseStatement()
Dim CurrentRow As Long
CurrentRow = Cells(Rows.Count, "C").End(xlUp).Row

'Application.ScreenUpdating = False 'Uncomment after testing

With Range("A1").CurrentRegion
Do While CurrentRow > 2
If Cells(CurrentRow, 1) = "" Then
CurrentRow = Collapse(CurrentRow)
Else: CurrentRow = CurrentRow - 1
End If
Loop
End With

Application.ScreenUpdating = True
End Sub

Private Function Collapse(CurrentRow As Long)
Dim DestRow As Long
DestRow = Range("A:A").Find("*", Cells(CurrentRow, "A"), , , xlByRows, xlPrevious).Row

Cells(CurrentRow, "D").Resize(, 2).Copy Cells(DestRow, "D")
Rows(DestRow + 1 & ":" & CurrentRow).Delete
Collapse = DestRow - 1
End Function

This does not affect the Balance column. If you need to move it with the Debt/Credit Rows change the "Resize(, 2)" to"Resize(, 3)"

[DavG63]

Sam

Sorry for the delay in responding, I didn't have a chance to test this until this morning. It works perfectly, so thank you very much for all your help.

[snb]

Or


Sub M_snb()
for each ar in columns(1).specialcells(4).areas
ar.cells(1).offset(-1,3)=ar.cells(ar.cells.count).offset(3).value
next

columns(1).specialcells(4).entirerow.delete
End Sub