applying formula on active cells

[whitehat86]

I have to do some calculations on the active cells after setting an autofilter.
And the calculation have to be done till the end of Row G as long as there is corresponding data in other cells.

The problem is that the cells that are not active (not visible due to the filter) also get filled by this formula.

What can I do? Some guidance plzz.

Sub Test1()

Dim x As Integer
Sheets("FM").Select
NumRows = Range("A4", Range("A4").End(xlDown)).Rows.Count
Range("G5").Select
For x = 1 To NumRows
ActiveCell.FormulaR1C1 = "=R[-1]C+RC[-3]"
ActiveCell.Offset(1, 0).Select
Next
End Sub

Thanking you in advance...

[Bob Phillips]

Try



Sub Test1()
Dim Numrows As Long
Sheets("FM").Select
Numrows = Range("A4", Range("A4").End(xlDown)).Rows.Count
Range("G5").Resize(Numrows - 4).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=R[-1]C+RC[-3]"
End Sub

[whitehat86]

Wow!
That was a super quick reply.

It's better now, but it's adding the cell on top with the cell on the side.

I want it to add the active cell on top with the active cell on the side.

How to mark the active cell right above?

[Bob Phillips]

Can you post your book and show what you mean?

[whitehat86]

Can you post your book and show what you mean?

1) Do a custom filter on collumn F, which contains all R1 (D1r1 and R1)
2) Cells G5 onwards contain the formula of (active cell above + corresonding cell beside in collumn D)
3) After removing the filter, I then need to fill the blank cells in between
with the corresponding value in the above cell.
*There is a test macro with the code that you had provided.
**In collumn O, I have pasted the values that should be there finally.

Thank you

[Bob Phillips]

My gosh, that is a tricky one



Sub Test1()
Const FORMULA_SUM_VISIBLE As String = _
"=INDEX(R1C7:R[-1]C7,MAX(IF(SUBTOTAL(3,OFFSET(INDEX(R1C4:R[-1]C4,1,1),ROW(R1C7:R[-1]C7)-ROW(INDEX(R1C7:R[-1]C7,1,1)),0))>0,ROW(R1C7:R[-1]C7))))+RC[-3]"
Dim Numrows As Long
Dim cell As Range
Sheets("FM Data").Select
Numrows = Range("C4", Range("C4").End(xlDown)).Rows.Count
For Each cell In Range("G5").Resize(Numrows - 1).SpecialCells(xlCellTypeVisible)

cell.FormulaArray = FORMULA_SUM_VISIBLE
Next cell
End Sub

[whitehat86]

My gosh, that is a tricky one



Sub Test1()
Const FORMULA_SUM_VISIBLE As String = _
"=INDEX(R1C7:R[-1]C7,MAX(IF(SUBTOTAL(3,OFFSET(INDEX(R1C4:R[-1]C4,1,1),ROW(R1C7:R[-1]C7)-ROW(INDEX(R1C7:R[-1]C7,1,1)),0))>0,ROW(R1C7:R[-1]C7))))+RC[-3]"
Dim Numrows As Long
Dim cell As Range
Sheets("FM Data").Select
Numrows = Range("C4", Range("C4").End(xlDown)).Rows.Count
For Each cell In Range("G5").Resize(Numrows - 1).SpecialCells(xlCellTypeVisible)

cell.FormulaArray = FORMULA_SUM_VISIBLE
Next cell
End Sub


The calculation seems correct.
But when I remove the filter, obvisouly the values change. Is there a way to keep the values even after i remove the filter?

Could you also explain briefly what you did in =INDEX(...

Thank you!

[whitehat86]

The calculation seems correct.
But when I remove the filter, obvisouly the values change. Is there a way to keep the values even after i remove the filter?

Could you also explain briefly what you did in =INDEX(...

Thank you!

Hey managed to get it working!! combined it with another macro that copied data in blank cells

Sub FillEm()
Dim LR As Long
LR = ActiveSheet.UsedRange.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
With Range("G4:G" & LR)
On Error Resume Next
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
On Error GoTo 0
.Value = .Value
End With
End Sub


But i would still appreciate an explanation of Index ...

[Bob Phillips]

INDEX was just getting the cell above that matched the criteria, in this case the last visible cell.

[whitehat86]

Thank you very much O Distinguished Lord of VBAX :D :)