I was having a lot of problems figuring out how you wanted the results
formated. the sample file didn't seem to be consitant with the formating. I
then tried to compare your code against the sample spreadsheet and still
didn't have a clear understanding what the results should look like.

"Kumar" wrote:

Thank you very much Joel it worked but as you said there's a Problem with
Formatting..

"joel" wrote:

I got the file and wrote the code to get your 1st results. See if this
helps. If you are still having problems let me know I will help. I can't
work on this problem any more today. See if you can get the 2nd results
yourself. You also may need to do some more formating with the 1st results.

Sub Output1()

Columns("A:A").Delete

Set c = Cells.Find(What:="account", _
LookIn:=xlValues, _
LookAt:=xlPart)
If Not c Is Nothing Then
Rows("1:" & (c.Row - 1)).Delete
End If

Set c = Cells.Find(What:="cash inflow", _
LookIn:=xlValues, _
LookAt:=xlPart)
If Not c Is Nothing Then
Rows("2:" & c.Row).Delete
End If

Set c = Cells.Find(What:="cash outflow", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart)
If Not c Is Nothing Then
c.EntireRow.ClearContents
c.MergeCells = False
End If

Columns("A").EntireColumn.Replace _
What:=" ", _
Replacement:="", _
LookAt:=xlPart

'Insert Header Rows and format
Rows(1).Insert
With Range("A1:C1")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.Merge
.Font.Bold = True
.Interior.ColorIndex = xlNone
.Font.ColorIndex = 0
End With

Range("A1") = "MIS REPORT FOR THE PERIOD OF"
Rows("3:4").Insert
With Rows("3:4").EntireRow
.Interior.ColorIndex = xlNone
.Font.ColorIndex = 0
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With

Range("A3") = "RECEIPTS"
Range("A4") = "OPENING BALANCE"

Set c = Cells.Find(What:="total (Rupees)", _
LookIn:=xlValues, _
LookAt:=xlPart)

If Not c Is Nothing Then
c.Value = "TOTAL"
Range(c, c.End(xlToRight)).Font.Bold = True
Set ReplaceRange = Range("B5:C" & c.Row)

ReplaceRange.Replace _
What:="cr", _
Replacement:="", _
LookAt:=xlPart
c.Offset(0, 2).Formula = _
"=SUM(C5:C" & (c.Row - 1) & ")"
End If

'-------------- End of Receipts --------------
'Find Last Row
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & LastRow) = "TOTAL"
'Add blank row
Rows(LastRow - 1).Insert
Range("A" & (LastRow - 1)) = "CLOSING BALANCES"
'clear previous row
Rows(LastRow - 2).ClearContents

Set c = Cells.Find(What:="expenses", _
LookIn:=xlValues, _
LookAt:=xlPart)
If Not c Is Nothing Then
StartExpenses = c.Row
End If

EndExpenses = c.Offset(1, 2).End(xlDown).Row - 1
Rows(EndExpenses + 1).Insert

LastRow = Range("A" & Rows.Count).End(xlUp).Row
Set ReplaceRange = _
Range("B" & StartExpenses & ":C" & LastRow)

ReplaceRange.Replace _
What:="dr", _
Replacement:="", _
LookAt:=xlPart

StartExpenseType = StartExpenses + 1
For RowCount = (StartExpenses + 1) To EndExpenses
If Range("B" & RowCount) = "" Then
ExpenseType = Range("A" & RowCount)
StartRow = RowCount + 1
End If
If Range("A" & RowCount) = "" Then
Range("A" & RowCount) = ExpenseType & " TOTAL"
Range("B" & RowCount) = ""
Range("C" & RowCount).Formula = _
"=Sum(B" & StartRow & ":B" & (RowCount - 1) & ")"
End If

Next RowCount

Range("C" & StartExpenses).Formula = _
"=Sum(C" & (StartExpenses + 1) & ":C" & EndExpenses & ")"

Range("C" & LastRow).Formula = _
"=Sum(C" & (StartExpenses + 1) & ":C" & (LastRow - 1) & ")"

End Sub


"joel" wrote:

I wasn't able to download the file. It looks like the file size is zero
bytes. I'm also not getting an error with the code I posted.

"Kumar" wrote:

Hey Joel You can Find my File at the Following Link:

http://www.easy-share.com/1904615394/Consolidated Cash flow.xls

Pls Help me in this Regard....

"joel" wrote:

I don't recommend using recorded macros without editing the code. Especially
when you end up with a macro this big. When using FIND set a variable to the
location so it is easier to code.

I fixed your total as best as I could. It looks like you are trying to add
all the values in a table. I think the table size may be veariable but your
could was using a fixed size table of 23 rows. Also fixed this problem. I
can't guarantee this code will work becaue of the large number of changes
that were made. If yo need more help let me know.


Sub Macro1()

Columns("A:A").Delete
Rows("1:8").Delete

Set c = Cells.Find(What:="cash inflow", _
LookIn:=xlFormulas, _
LookAt:=xlPart)
If Not c Is Nothing Then
c.EntireRow.Delete
End If

Set c = Cells.Find(What:="cash outflow", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart)
If Not c Is Nothing Then
c.EntireRow.Delete
End If

Columns("A").EntireColumn.Replace _
What:=" ", _
Replacement:="", _
LookAt:=xlPart

Cells.Replace _
What:="account", _
Replacement:="Particulars", _
LookAt:=xlPart

Cells.Replace _
What:="Details", _
Replacement:="Amount", _
LookAt:=xlPart

Set c = Cells.Find(What:="b/f", _
LookIn:=xlFormulas, _
LookAt:=xlPart)

If Not c Is Nothing Then
Rows("1:2").EntireRow.Insert
End If

With Rows("1:2")
.Interior.ColorIndex = xlNone
.Font.ColorIndex = 0
End With

Range("A1") = "RECEIPTS"

Range("B1") = "OPENING BALANCE"

Set c = Cells.Find(What:="receipts", _
LookIn:=xlFormulas, _
LookAt:=xlPart)

If Not c Is Nothing Then
c.EntireRow.Delete
With c.Offset(-1, 0).Rows("1:2").EntireRow
.Interior.ColorIndex = xlNone
.Font.ColorIndex = 0
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With

c.Offset(0, 1) = "(Rs.)"
c.Offset(0, 2) = "(Rs.)"
End If

Rows(1).Insert
Range("A1") = "MIS REPORT FOR THE PERIOD OF"

With Range("A1:C1")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False

.Merge
.Font.Bold = True

End With

Range("A6:C7").Font.Bold = True

Set c = Cells.Find(What:="income", _
LookIn:=xlFormulas, _
LookAt:=xlPart)

If Not c Is Nothing Then
c.Range("A1:C1").Font.Bold = True
End If

Set c = Cells.Find(What:="total (Rupees)", _
LookIn:=xlFormulas, _
LookAt:=xlPart)

If Not c Is Nothing Then
Range(c, c.End(xlToRight)).Font.Bold = True
Set ReplaceRange = Range(c, c.End(xlUp))
ReplaceRange.Replace _
What:="cr", _
Replacement:="", _
LookAt:=xlPart
c.Offset(0, 2).FormulaR1C1 = "=SUM(R[-6]C:R[-1]C)"
End If


Set c = Cells.Find(What:="expenses", _
LookIn:=xlFormulas, _
LookAt:=xlPart)
If Not c Is Nothing Then
c.EntireRow.Insert
Set c = c.Offset(1, 0)
Set LastCol = c.End(xlToRight)
Set LastCell = LastCol.End(xlDown)