VBA Find and replace values with formulas

[Jim123]

I am stuck on a section of my macro. I have come to a spot where in column C I have values ranging from 0-999 never in the same order or number of records, Any value that is less than 10 I need to replace with the sum of the adjacent cells in column A+B. example C5 = sum A5+B5.

I am not sure if the find and replace function is correct for what I need to do. I was also looking at filtering the <10 then adding the formula but not sure how to do that either.

Thanks for any help
newb

 

[VoG]

Try

Sub AddForm()
Dim LR As Long, i As Long
LR = Range("C" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("C" & i)
        If .Value < 10 Then .Formula = "=" & .Offset(, -2).Address(False, False) & "+" & .Offset(, -1).Address(False, False)
    End With
Next i
End Sub

 

[Jim123]

That worked great. Thank you very much

 

[p45cal]

At its simplest this will put formulae in all cells in column C with a value less than 10:

  For Each cll In Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns("C:C")).Cells
    If cll.Value < 10 Then cll.FormulaR1C1 = "=RC[-2]+RC[-1]"
  Next cll

but this will include blank cells too, so the next step is to only process thos cells which already contain something, so instead:

With Columns("C:C")
  For Each cll In Union(.SpecialCells(xlCellTypeConstants, 23), .SpecialCells(xlCellTypeFormulas, 23)).Cells
    If cll.Value < 10 Then cll.FormulaR1C1 = "=RC[-2]+RC[-1]"
  Next cll
End With

The specialcells method can be adjusted to suit, (it's the vba equivalent to pressing F5, clicking on Special then choosing what you want)