change code

[keri]

I have the below code kindly provided to me by a user of this forum

Sub deleteincomplete()
'delete all rows that have incomplete data
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirst As String
Dim i As Integer
For i = 1 To answer
Set rngToSearch = Sheets("cardata" & i).Columns("I") 'Change
this...
Set rngFound = rngToSearch.Find(what:="incomplete", _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)
If Not rngFound Is Nothing Then
Set rngFoundAll = rngFound
strFirst = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirst
rngFoundAll.EntireRow.Delete
End If
Next i

The idea is that if one of my rows that are being searched contains
"incomplete" that the whole row is deleted. I have now found I need to
keep this row, but delete where is says incomplete and enter the word
"incomplete" into column B. I then need to copy the data from columns C
+ E + G on this row into column I.

I am not sure where to start writing this code into the rngfoundAll row
in the code above.

Many thanks.

 

[keri]

I have written this code;

rngFoundAll.Formula = "=sum(C+E+G)"
rngFoundAll.Offset(0, -7) = "incomplete"

to do the above instead of the deleterow line. However this enters this
formula into the cell;

=SUM(I:I+E+G)

and I am not sure how to solve this as obviously this returns a
circular reference, plus i need to refer to the row number in the
formula.

 

[keri]

Thanks for your help. I had just puzzled out the below code so it's
nice to know I was on the right lines!

rngFoundAll.Formula = "=RC[-6]+RC[-4]+RC[-2]"
rngFoundAll.Offset(0, -7) = "incomplete"