Help Please: Need code to test several lines

[bruce forster]

This is a similar post to one yesterday but with the new infor. someone might be able to help

I am creating a financial projection model and need a single line of code that will run a formula based on an assumption "driver" unique to each line item in the financial statement. For instance, if C2 is "Input" then do "x" if C2 is "% of Revenue" do "y" etc and this test must run for each line in the financial statement

Here is what I have written but i get a "type mismatch" error and the same # is returned for each line item.. Please help

Sub ProjCal(
If Sheets("IncStmtAssump").Range("C2:C50").Value = "Input" Then Sheets("Sheet3").Range("B3:B50").Formula = "='IncStmtAssump'!D2
If Sheets("IncStmtAssump").Range("C2:C50").Value = "% of Revenue" Then Sheets("Sheet3").Range("B3:B50").Formula = "='IncStmtAssump'!D2*'Sheet3'!D2
End Su

In advance Thanks.

 

[Tom Ogilvy]

Here is your problem

If Sheets("IncStmtAssump").Range("C2:C50").Value = "Input"

will give a type mismatch error since you are comparing an array to a
string.

You need to check one cell at a time.

--
Regards,
Tom Ogilvy

This is a similar post to one yesterday but with the new infor. someone might be able to help.

I am creating a financial projection model and need a single line of code

that will run a formula based on an assumption "driver" unique to each line
item in the financial statement. For instance, if C2 is "Input" then do "x"
if C2 is "% of Revenue" do "y" etc and this test must run for each line in
the financial statement.

Here is what I have written but i get a "type mismatch" error and the same

# is returned for each line item.. Please help.

Sub ProjCal()
If Sheets("IncStmtAssump").Range("C2:C50").Value = "Input" Then

Sheets("Sheet3").Range("B3:B50").Formula = "='IncStmtAssump'!D2"

If Sheets("IncStmtAssump").Range("C2:C50").Value = "% of Revenue"

Then Sheets("Sheet3").Range("B3:B50").Formula =
"='IncStmtAssump'!D2*'Sheet3'!D2"

 

[bruce forster]

Can you suggest some code for a loop.

 

[Tom Ogilvy]

Something like:

for each cell in Sheets("IncStmtAssump").Range("C2:C50")
if cell.Value = "Input" then
Worksheets("Sheet3").Range( _
cell.offset(0,1).Address).Formula = _
"='IncStmtAssump'!D" & cell.row
End if
Next