There must be an eaiser way

[Guest]

I am creating a financial projection model which includes creation of a projected income statement where the user chooses from 5 different assumption "drivers" for each line item. I attempted to write a single line of code that would apply an IF-THEN statement to each line but ended up having to write a line of code for each line item (Revenue, COGS, Op.Exp, etc) for each year, 250 lines of code!!! Here is what I tried to write

Sub Test_Click(
If Sheets("Sheet1").Range("A1:A50").Value = "Input" Then Sheets("Sheet2").Range("B1:B50").formula =
"=Sheets("Sheet1").Range("B1:B50"
End Su

What I was trying to accomplish is have the code look at each individual line item assumption and if it was "Input" place the contents of the corresponding cell in the projected year

Here is a sample of what I had to do to get to work. Really slows my program down

Sub FormulaTest_Click(
If Range("B2").Value = "Input" Then Sheets("Sheet3").Range("B3").Formula = "=IncStmtAssump!C2
If Range("B2").Value = "% of Revenue" Then Sheets("Sheet3").Range("B3").Formula = "=IncStmtAssump!C2*Sheet3!B3
If Range("B2").Value = "% Change from Previous Year" Then Sheets("Sheet3").Range("B3").Formula = "=(1+IncStmtAssump!C2)*Sheet1!H3
If Range("B3").Value = "Input" Then Sheets("Sheet3").Range("B4").Formula = "=IncStmtAssump!C3
If Range("B3").Value = "% of Revenue" Then Sheets("Sheet3").Range("B4").Formula = "=IncStmtAssump!C3*Sheet3!B3
If Range("B3").Value = "% Change from Previous Year" Then Sheets("Sheet3").Range("B4").Formula = "=(1+IncStmtAssump!C3)*Sheet1!H4

There must be a way to get the original code idea to work..Please advise.

 

[Tom Ogilvy]

You could enter you text strings in a column in a table and then put your
lookup values to the left. Then use the vlookup function to return the
formula string you want.

Input "=IncStmtAssump!C"
% of Revenue "=IncStmtAssump!C" "*Sheet3!B3"


You could lookup the strings and built your formula

sformula = String1 & row & String 2

--
Regards,
Tom Ogilvy

I am creating a financial projection model which includes creation of a

projected income statement where the user chooses from 5 different
assumption "drivers" for each line item. I attempted to write a single line
of code that would apply an IF-THEN statement to each line but ended up
having to write a line of code for each line item (Revenue, COGS, Op.Exp,
etc) for each year, 250 lines of code!!! Here is what I tried to write;

Sub Test_Click()
If Sheets("Sheet1").Range("A1:A50").Value = "Input" Then

Sheets("Sheet2").Range("B1:B50").formula = _

"=Sheets("Sheet1").Range("B1:B50")
End Sub

What I was trying to accomplish is have the code look at each individual

line item assumption and if it was "Input" place the contents of the
corresponding cell in the projected year.

Here is a sample of what I had to do to get to work. Really slows my program down.

Sub FormulaTest_Click()
If Range("B2").Value = "Input" Then

Sheets("Sheet3").Range("B3").Formula = "=IncStmtAssump!C2"

If Range("B2").Value = "% of Revenue" Then

Sheets("Sheet3").Range("B3").Formula = "=IncStmtAssump!C2*Sheet3!B3"

If Range("B2").Value = "% Change from Previous Year" Then

Sheets("Sheet3").Range("B3").Formula = "=(1+IncStmtAssump!C2)*Sheet1!H3"

If Range("B3").Value = "Input" Then

Sheets("Sheet3").Range("B4").Formula = "=IncStmtAssump!C3"

If Range("B3").Value = "% of Revenue" Then

Sheets("Sheet3").Range("B4").Formula = "=IncStmtAssump!C3*Sheet3!B3"

If Range("B3").Value = "% Change from Previous Year" Then

Sheets("Sheet3").Range("B4").Formula = "=(1+IncStmtAssump!C3)*Sheet1!H4"

 

[Guest]

Tom: Thanks for the response but I did not understand it. Here is some code I am trying in an effort to have each line item evaluated against the assumption on the corresponding line ie "input" but I am getting a type mismatch error. I really need this to work to move forwar

Sub FormulaTest_Click(
If Sheets("IncStmtAssump").Range("B2:B50").Value = "" Then Sheets("Sheet3").Range("B3:B50") = "
If Sheets("IncStmtAssump").Range("B2:B50").Value = "Input" Then Sheets("Sheet3").Range("B3:B50").Formula = "=IncStmtAssump!C2
End Su