# 2 questions

#### alokjain22

##### New Member
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?

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Try this:

Code:
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.

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

Replies
3
Views
101
Replies
6
Views
198
Replies
0
Views
203
Replies
8
Views
669
Replies
14
Views
479

1,211,839
Messages
6,104,290
Members
447,901
Latest member
boy3hc2004

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back