Solved: change formula

[Pete]

Hi Experts

Need to reflect the following formula in the format as shown below

change this

{=((Pricing Demand F6 * (1-Shipping BOG F6)) * Modelling (Vol) F6)}


TO this format

.Cells(startrow + 1, curCol).Formula = _
"=SUM(" & _
"OFFSET(" & _
"'CashFlow Q4'!$F" & startrow + 1 & ", " & _
"0, " & _
"(INT(RIGHT(" & .Cells(5, curCol).Address & ", 2))-INT(RIGHT('CashFlow Q4'!$F$5)))*4, " & _
"1, " & _
"4" & _
")" & _
")"

[Bob Phillips]

You mean you want linefeeds within the formula? If so, which one, the firts or the second?

[Pete]

second one. please....i have got this far but think its crap....

.Cells(startrow + 1, curCol).Formula = _
"=SUM(" & _
"'Pricing Demand'!$F" & startrow + 1 & ", " & _
+ & _
"(1-'Shipping BOG'!$F & startrow + 1 & ", " &_ )) & _
* & _
"('Modelling (Vol)'!$F & startrow + 1 & ", " & _)"

[Pete]

Apologies the first one..........

[Bob Phillips]

Cells(startrow + 1, curCol).Formula = _
"=SUM(" & _
"'Pricing Demand'!$F" & startrow + 1 & ", " & Chr(10) & _
"(1-'Shipping BOG'!$F" & startrow + 1 & "))" & Chr(10) & _
"*" & Chr(10) & _
"('Modelling (Vol)'!$F" & startrow + 1 & ")"

[Pete]

Let me test it

[Pete]

Ok here is the full vba code not working.....

Sub iniArray()
Dim sup As String
Dim dem As String
Dim rw As Long

With Sheets("Revenue")
For rw = 6 To .Cells(.Rows.Count, "B").End(xlUp).Row
If .Range("f" & rw).Interior.Color = vbWhite Then
sup = .Range("b" & rw)
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 = .Range("c" & rw)
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(currow + 1, "E") = "Rev_" & sup & "_" & dem

'Loop through each column in current row
curCol = 6
While (.Cells(5, curCol).Value <> "")

'Assign formula
Cells(startrow + 1, curCol).Formula = _
"=SUM(" & _
"'Pricing Demand'!$F" & startrow + 1 & ", " & Chr(10) & _
"(1-'Shipping BOG'!$F" & startrow + 1 & "))" & Chr(10) & _
"*" & Chr(10) & _
"('Modelling (Vol)'!$F" & startrow + 1 & ")"

curCol = curCol + 1

Wend

'Assign the number format
Range(.Cells(currow + 1, str1), .Cells(currow + 1, "EC")).NumberFormat = "#,##0.00_ ;(#,##0.00);-"

'Check if end of section
If .Cells(currow, str1).Value <> strData Then
currow = currow + 1
Exit For
End If
Next

With .Range("f" & rw & ":EC" & rw)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="1"
.FormatConditions(1).Font.ColorIndex = 1
.FormatConditions(1).Interior.ColorIndex = 34
.FormatConditions(1).NumberFormat = "#,##0.00_ ;[Red](#,##0.00);-"
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="0"
.FormatConditions(2).Font.ColorIndex = 15
.FormatConditions(2).Interior.Pattern = xlNone
.FormatConditions(2).NumberFormat = "#,##0.00_ ;[Red](#,##0.00);-"
End With
Next
End With

End Sub

[Bob Phillips]

More detail, I don't do mind-reading.

[Pete]

Apologies...........should have been more clear from the start...Sorry

[Pete]

No luck............

[Bob Phillips]

No I am asking for more detail, you said it doesn't work. That tells me nothing.

[Pete]

fixed it...........

"=((" & _
"'Pricing Demand'!$F" & startrow + 1 & " * " & Chr(10) & _
"(1-'Shipping BOG'!$F" & startrow + 1 & "))" & Chr(10) & _
"*" & Chr(10) & _
"'Modelling (Vol)'!$F" & startrow + 1 & ")"