Formula Change

[Guest]

I'm looking for a change to this formula. Currently it will add the formula
to the range of cells, but it only calculates the first row of data in ALL
the cells. Instead of auto filling the formula and changing for the
appropriate row.

Sub WORKING()

Dim wks As Worksheet
Dim LastCol As Long
Dim FirstRow As Long
Dim FirstCol As Long

Set wks = Worksheets("MASTER")
With wks
FirstRow = 2
FirstCol = 4
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

' this section needs to be changed in some way

Range(Cells(2, LastColumn + 1), Cells(LastRow, LastColumn + 1)) =
Application.Sum _
(Cells(2, LastCol) - Cells(2, FirstCol - 2))

End Sub

I'm not sure how to get it to fill that formula and change the row for the
appropriate row. Make sense?

Example:

A B C D E F G H I
# 50 .71 40 48 88 38

Column I is the result from subtracting B(firstCol - 2) from column H (the
lastCol). That works correct. Now if I have that formula fill in all of row
all the cells in that column will be 38.

ANY help is appreciated, let me know if you need more info. Thanks!!!

 

[Bob Phillips]

You are getting LastCol and LastColumn mixed up

Range(Cells(2, LastCol + 1), Cells(LastRow, LastCol + 1)).FormulaR1C1 =
"=SUM(RC" & FirstCol & ":RC" & LastCol & ")"


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)