# If Formula with text and numbers

#### dilshod_k

##### New Member
Hi there,
I'm trying to write code for backtesting of trading strategy.
Given:
Active Worksheet with:
Range C2:C200 with range of Text values: Buy, Sell or empty cell
Range B2:B200 with Closing Price
Lets assume B2= 7.30 is a price per share

Reference Worksheet ("Control") with:
Range.B("10").Value= 1000 is a Buying power in USD
Range.B("11").Value=5 is commission per trade in USD

How to write formula with conditions as it follows:

if C2 = "Buy" Then (1000-5)/7.30 write down result in D2 - whole number!
if C2 = "Sell" Then (D2*7.30)-5 write down result in E2
if C2 = empty cell then do nothing

#### jproffer

##### Well-known Member
See if that does what you want.

#### dilshod_k

##### New Member
See if that does what you want.

BCDE
2\$7.30Sell992.8

</tbody>
Sheet2

Worksheet Formulas
CellFormula
E2=IF(C2="Sell",(ROUNDDOWN((Control!B10-Control!B11)/B2,0))*B2,"")

</tbody>

<tbody>
</tbody>
Thanks a lot for a prompt response.
I'm afraid I was not able to use this formula as I need to use it as VBA code.
Apparently I did not described properly the whole story.
I've got excel file with VBA code that allows download of historical prices for hundreds if not thousands of shares. For each share code creates separate sheet with name=symbol of the share. After that code makes calculations (for each/and on each sheet separately) based on several indicators and gives signals: Buy, Sell or empty cell.
I tried to write code But as I'm just doing first steps in VBA coding I can't write with proper syntax, so I use combination of Macros recording with manual adjustments.
Here is the sample of the code I tried to write:

Dim ws As Worksheet

For Each ws In Sheets

If ws.Name <> "Control" And ws.Name <> "Response" Then

ws.Activate

Range("Q1").Select
Selection.NumberFormat = "?#,##0.00"
ActiveCell.FormulaR1C1 = "EQUITY"
Range("Q2").Select
ActiveCell.FormulaR1C1 = _
Range("Q2").Select
Selection.AutoFill Destination:=Range("Q2:Q150")
Range("R1").Select
Selection.NumberFormat = "?#,##0.00"
ActiveCell.FormulaR1C1 = "(USD)"
Range("R2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-4].value = ""SELL"",Roundown((ActiveSheet!D2*ActiveSheet!B2) - Control!B11,0),""""))"
Range("R2").Select
Selection.AutoFill Destination:=Range("R2:R150")

Range("R2").Select

End If

1,082,099
Messages
5,363,129
Members
400,720
Latest member
Pettel

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...