2 questions

[alokjain22]

I am good in excel. However i do not know how to solve the following two problems:-

1. Say i have a formula in Column C = "ColumnA+ColumnB (a1+b1, a2+b2, ... like that in all rows of column C. Now i want to add an 'IF' condition in the same row. (e.g.=if(a1+b1>100,100,a1+b1). One way of doing is entering this formula and copy it in all rows of col C. But what if Column C have different formulas, and i have to add the 'IF' condition (or any ther thing) in all rows of col C irrespective of difference of formulas.

2. In a Macro, how to choose the End of the sheet, when it is not a fixed cell?

 

[Andrew Poulsom]

Try this:

Sub Test()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim Cell As Range
    Set Sh = Worksheets("Sheet1")
    With Sh
        Set Rng = .Range("C1:C" & .Range("C" & .Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeFormulas)
    End With
    For Each Cell In Rng
        With Cell
            .FormulaR1C1 = "=IF((RC[-2]+RC[-1])>100,100," & Replace(.FormulaR1C1, "=", "") & ")"
        End With
    Next Cell
End Sub

In the sixth line, this:

.Range("C" & .Rows.Count).End(xlUp)

returns the last used cell in column C.

 

[pgc01]

Hi Andrew

I was thinking that instead of

Replace(.FormulaR1C1, "=", "")

maybe

Mid(.FormulaR1C1, 2)

in case the formula has other "=" besides the first.

Cheers
PGC