Problem with formula in VBA

[rpagano]

Hello all! This is my first post here.

I want to subtract Revenues from Expenses to find a P&L number. The code I used below is to find the row number where Revenues and Expenses are in the Excel file. The formula is where I am having trouble. The formula is being entered in cell H90.

Dim Rev As Integer
Dim Exp As Integer
Rev = Application.WorksheetFunction.Match("Revenues", Range("C:C"), 0)
Exp = Application.WorksheetFunction.Match("Expenses", Range("C:C"), 0)
Range("H90").Select
ActiveCell.Formula = "=H[Rev]-H[Exp]"

The formula is clearly wrong, but I am unsure how to reference those Match values in the formula. Any help would be appreciated.

Thanks,

Ryan

 

[Jonmo1]

Welcome to the board..

Try

ActiveCell.Formula = "=H" & Rev & "-H" & Exp


Also, you don't have to select the cell, and then use Activecell...
You can just put the formula in the cell directly..


Instead of
Range("H90").Select
ActiveCell.Formula = "=H" & Rev & "-H" & Exp

You can just do
Range("H90").Formula = "=H" & Rev & "-H" & Exp


Hope that helps.

 

[rpagano]

It does! Thanks a ton!