Pete
07-16-2008, 09:27 AM
see atatched workbook.......book1.xls
Hi Experts
firstly igonre the fact that the formula in column F6 onwards to column BC say "SIL Portfolio 2008v7.xls....
here is the formula with the reference to where the link is:
=(('Pricing Demand'!F6-('Pricing Supply'!F6+'Shipping UFC'!F6)/(1-'Shipping BOG'!F6))*'Modelling (Vol)'!F6)
so here is the scenario: (sheet 1)
1. If the user selects in column A FOB then keep the about formula..
2. If the user selects in column A DES then amend formula to
=('Pricing Demand'!F6-'Pricing Supply'!F6+'Shipping UFC'!F6)*'Modelling (Vol)'!F6)
below is the fulll vba code that operates the creation of the formula with the shipping costs included, but need to amend the code so it allows the user to switch between formula depending if DES or FOB is selected from the drop down list in column A
see code below
Sub alaeod_improved_2(wsName As String) '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 col As Long Dim sup As String Dim dem As String Dim ws As Worksheet Dim curCol As Integer Dim lngCalculationType As Long lngCalculationType = Application.Calculation Application.Calculation = xlCalculationManual str1 = "B" Set ws = Worksheets(wsName) With ws lastrow = .Cells(.Cells.Rows.Count, str1).End(xlUp).Row strData = .Cells(lastrow, str1).Value If (wsName = "CashFlow") Then For startrow = lastrow - 1 To 1 Step -1 'Get the supply & demand information 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, ")", "") 'Generate the cell name .Cells(startrow + 1, "E") = "CF_" & sup & "_" & dem 'Loop through each column in current row curCol = 6 While (.Cells(5, curCol).Value <> "") 'Assign formula .Cells(startrow + 1, curCol).Formula = Replace("=(('Pricing Demand'!^1" & startrow + 1 & " - ('Pricing Supply'!^1" & startrow + 1 & " + 'Shipping UFC'!^1" & startrow + 1 & ")/(1-'Shipping BOG'!^1" & startrow + 1 & "))* 'Modelling (Vol)'!^1" & startrow + 1 & ")", "^1", Mid(Cells(1, 6).Address, 2, InStr(2, Cells(1, 6).Address, "$") - 2)) curCol = curCol + 1 Wend 'Assign the number format Range(.Cells(startrow + 1, str1), .Cells(startrow + 1, "EC")).NumberFormat = "#,##0.00_ ;(#,##0.00);-" 'Check if end of section If .Cells(startrow, str1).Value <> strData Then startrow = startrow + 1 Exit For End If Next 'Put in summary line for worksheet' .Cells(lastrow + 1, str1).EntireRow.Insert' lastrow = lastrow + 1' With Range(.Cells(lastrow, str1), .Cells(lastrow, "EC"))' .Interior.Color = RGB(255, 195, 127)' .FormatConditions.Delete' .NumberFormat = "#,##0.00_ ;(#,##0.00);-"' With .Borders(xlEdgeBottom)' .LineStyle = xlContinuous' .Weight = xlMedium' .ColorIndex = xlAutomatic' End With' With .Borders(xlEdgeTop)' .LineStyle = xlContinuous' .Weight = xlMedium' .ColorIndex = xlAutomatic' End With' End With Set rng = Range(.Cells(startrow, str1), .Cells(lastrow, "EC")) rng.NumberFormat = "#,##0.00_ ;(#,##0.00);-" 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 ws Set rng = Range(.Cells(startrow, str1), .Cells(lastrow, "F")) With rng.Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End WithEnd If Application.Calculation = lngCalculationTypeEnd With End Sub
Hi Experts
firstly igonre the fact that the formula in column F6 onwards to column BC say "SIL Portfolio 2008v7.xls....
here is the formula with the reference to where the link is:
=(('Pricing Demand'!F6-('Pricing Supply'!F6+'Shipping UFC'!F6)/(1-'Shipping BOG'!F6))*'Modelling (Vol)'!F6)
so here is the scenario: (sheet 1)
1. If the user selects in column A FOB then keep the about formula..
2. If the user selects in column A DES then amend formula to
=('Pricing Demand'!F6-'Pricing Supply'!F6+'Shipping UFC'!F6)*'Modelling (Vol)'!F6)
below is the fulll vba code that operates the creation of the formula with the shipping costs included, but need to amend the code so it allows the user to switch between formula depending if DES or FOB is selected from the drop down list in column A
see code below
Sub alaeod_improved_2(wsName As String) '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 col As Long Dim sup As String Dim dem As String Dim ws As Worksheet Dim curCol As Integer Dim lngCalculationType As Long lngCalculationType = Application.Calculation Application.Calculation = xlCalculationManual str1 = "B" Set ws = Worksheets(wsName) With ws lastrow = .Cells(.Cells.Rows.Count, str1).End(xlUp).Row strData = .Cells(lastrow, str1).Value If (wsName = "CashFlow") Then For startrow = lastrow - 1 To 1 Step -1 'Get the supply & demand information 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, ")", "") 'Generate the cell name .Cells(startrow + 1, "E") = "CF_" & sup & "_" & dem 'Loop through each column in current row curCol = 6 While (.Cells(5, curCol).Value <> "") 'Assign formula .Cells(startrow + 1, curCol).Formula = Replace("=(('Pricing Demand'!^1" & startrow + 1 & " - ('Pricing Supply'!^1" & startrow + 1 & " + 'Shipping UFC'!^1" & startrow + 1 & ")/(1-'Shipping BOG'!^1" & startrow + 1 & "))* 'Modelling (Vol)'!^1" & startrow + 1 & ")", "^1", Mid(Cells(1, 6).Address, 2, InStr(2, Cells(1, 6).Address, "$") - 2)) curCol = curCol + 1 Wend 'Assign the number format Range(.Cells(startrow + 1, str1), .Cells(startrow + 1, "EC")).NumberFormat = "#,##0.00_ ;(#,##0.00);-" 'Check if end of section If .Cells(startrow, str1).Value <> strData Then startrow = startrow + 1 Exit For End If Next 'Put in summary line for worksheet' .Cells(lastrow + 1, str1).EntireRow.Insert' lastrow = lastrow + 1' With Range(.Cells(lastrow, str1), .Cells(lastrow, "EC"))' .Interior.Color = RGB(255, 195, 127)' .FormatConditions.Delete' .NumberFormat = "#,##0.00_ ;(#,##0.00);-"' With .Borders(xlEdgeBottom)' .LineStyle = xlContinuous' .Weight = xlMedium' .ColorIndex = xlAutomatic' End With' With .Borders(xlEdgeTop)' .LineStyle = xlContinuous' .Weight = xlMedium' .ColorIndex = xlAutomatic' End With' End With Set rng = Range(.Cells(startrow, str1), .Cells(lastrow, "EC")) rng.NumberFormat = "#,##0.00_ ;(#,##0.00);-" 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 ws Set rng = Range(.Cells(startrow, str1), .Cells(lastrow, "F")) With rng.Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End WithEnd If Application.Calculation = lngCalculationTypeEnd With End Sub