Solved: Amend Macro in worksheet Cashflow Yearly

[Pete]

Need to slightly re design the following workbook......

1. Amend VBA Code worksheet "Cashflow Yearly"....

need to the macro to remain the same OTHER than remove the part of generating the same forumla in each section i.e. see hightlighted....in yellow as to worksheet "Cashflow Q4".....

so that know when the macro is run the formula in cell F6 is = CashFlow Q4$F$6
and NOT that longer array formula currently in there at the moment...........

Looking at way to simplify the workbook.........

[Pete]

attached workbook

[Bob Phillips]

That formula is not an array formula even thoough you have array-entered it.

All the formulae return an error as linked to another workbook.

The code you supply dosen't generate that formula.

So the question was again ...?

[Pete]

cell F6 is = CashFlow Q4$F$6 in worksheet Cashflow Yearly.......and no long complicated formulas in worksheet "cashflows Yearly".....

[Bob Phillips]

??????????

[Pete]

ok here is the code apologies for the confusion


Sub alaeod_6() 'Add Line At End Of Data
Dim str1 As String
Dim lastrow As Long
Dim rng As Range
Dim startrow As Long
Dim strData As String
Dim sup As String
Dim dem As String

str1 = "B"
With Sheets("CashFlow Yearly")
lastrow = .Cells(.Cells.Rows.Count, str1).End(xlUp).Row
strData = .Cells(lastrow, str1).Value
For startrow = lastrow - 1 To 1 Step -1
sup = .Cells(startrow + 1, "B").Value
sup = Replace(sup, "+", "_plus")
sup = Replace(sup, ", ", "~")
sup = Replace(sup, " ", "_")
sup = Replace(sup, "~", ", ")
sup = Replace(sup, "-", "_")
sup = Replace(sup, "/", "_")
sup = Replace(sup, "(", "")
sup = Replace(sup, ")", "")
dem = .Cells(startrow + 1, "C").Value
dem = Replace(dem, "+", "_plus")
dem = Replace(dem, ", ", "~")
dem = Replace(dem, " ", "_")
dem = Replace(dem, "~", ", ")
dem = Replace(dem, "-", "_")
dem = Replace(dem, "/", "_")
dem = Replace(dem, "(", "")
dem = Replace(dem, ")", "")
.Cells(startrow + 1, "E") = "CF_" & sup & "_" & dem
.Cells(startrow + 1, "F").Resize(1, 48).FormulaArray = _
"=(Vol_" & sup & "_" & dem & _
" * " & "Price_" & dem & "_" & sup & ") - (" & _
"Vol_" & sup & "_" & dem & " * (" & _
"Price_" & sup & "_" & dem & " + " & _
"UFC_" & sup & "_" & dem & "))"
Range(.Cells(startrow, str1), .Cells(startrow, "BA")).NumberFormat = "#,##0.00_ ;-#,##0.00;-"
If .Cells(startrow, str1).Value <> strData Then
startrow = startrow + 1
Exit For
End If
Next
Set rng = Range(.Cells(startrow, str1), .Cells(lastrow, "BA"))
rng.NumberFormat = "#,##0.00_ ;-#,##0.00;-"
End With

With rng.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With rng.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With rng.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

With Sheets("CashFlow Yearly")
Set rng = Range(.Cells(startrow, str1), .Cells(lastrow, "F"))

With rng.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With

End Sub

[Aussiebear]

Lets start this conversation once again Pete, by clearly stating what it is you are chasing (step by step) and what it is that you want the data to look like.

Until you do that most people are simply going to step around your thread.

[mdmackillop]

Lets start this conversation once again Pete, by clearly stating what it is you are chasing (step by step) and what it is that you want the data to look like.

Until you do that most people are simply going to step around your thread.
Agreed,
I've read post #1 three times now, and have no idea what is required.

[Pete]

Solved it.....might have taken 8 hours

Sub alaeod_6() 'Add Line At End Of Data
Dim str1 As String
Dim lastrow As Long
Dim rng As Range
Dim startrow As Long
Dim strData As String
Dim sup As String
Dim dem As String

str1 = "B"
With Sheets("CashFlow Yearly")
lastrow = .Cells(.Cells.Rows.Count, str1).End(xlUp).Row
strData = .Cells(lastrow, str1).Value
For startrow = lastrow - 1 To 1 Step -1
sup = .Cells(startrow + 1, "B").Value
sup = Replace(sup, "+", "_plus")
sup = Replace(sup, ", ", "~")
sup = Replace(sup, " ", "_")
sup = Replace(sup, "~", ", ")
sup = Replace(sup, "-", "_")
sup = Replace(sup, "/", "_")
sup = Replace(sup, "(", "")
sup = Replace(sup, ")", "")
dem = .Cells(startrow + 1, "C").Value
dem = Replace(dem, "+", "_plus")
dem = Replace(dem, ", ", "~")
dem = Replace(dem, " ", "_")
dem = Replace(dem, "~", ", ")
dem = Replace(dem, "-", "_")
dem = Replace(dem, "/", "_")
dem = Replace(dem, "(", "")
dem = Replace(dem, ")", "")
.Cells(startrow + 1, "E") = "CF_" & sup & "_" & dem
.Cells(startrow + 1, "F").Resize(1, 48).FormulaArray = _
"=if(iserror((Revenue_" & dem & "_" & sup & _
" - " & "Purchase_" & sup & "_" & dem & ")),0," & _
"(Revenue_" & dem & "_" & sup & _
" - " & "Purchase_" & sup & "_" & dem & "))"

Range(.Cells(startrow, str1), .Cells(startrow, "BA")).NumberFormat = "#,##0.00_ ;-#,##0.00;-"
If .Cells(startrow, str1).Value <> strData Then
startrow = startrow + 1
Exit For
End If
Next
Set rng = Range(.Cells(startrow, str1), .Cells(lastrow, "BA"))
rng.NumberFormat = "#,##0.00_ ;-#,##0.00;-"
End With

With rng.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With rng.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With rng.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

With Sheets("CashFlow Yearly")
Set rng = Range(.Cells(startrow, str1), .Cells(lastrow, "F"))

With rng.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With

End Sub

[Aussiebear]

Pete, Great to see you have been able to provide the solution, however if you post code to this forum would you please use the VBA tags option, as this makes the code a whole lot easier to read. OK?