Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
change code
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
change code
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
change code
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" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code Conflicts With Worksheet Change Code | Excel Programming | |||
change DAO code to ADO | Excel Programming | |||
VB CODE RUN ON DDE VALUE CHANGE | Excel Programming | |||
VB CODE RUN ON DDE VALUE CHANGE | Excel Programming | |||
Change to Code | Excel Programming |