VBA Find and replace values with formulas

Jim123

New Member
Joined
Mar 5, 2011
Messages
4
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

Legend
Joined
Jun 19, 2002
Messages
63,651
Try

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

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
At its simplest this will put formulae in all cells in column C with a value less than 10:
Code:
  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:
Code:
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)
 

Forum statistics

Threads
1,081,517
Messages
5,359,234
Members
400,523
Latest member
ExcelNewbie98

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top