Remove Formulas then Delete Cells based on conditions - XL2003

[bambier]

There may be more information here than is needed, but I figure too
much is better than not enough!


I have multiple sheets in a workbook that keep track of files and
their status in a workflow.
Excel version is 2003

----------
Background and Sheet Explanation:
----------


The FolderLog tab keeps a list of all the jobs available, dates of any
status changes and identifies their current status.

The ReaderLog tab keeps a list of all the jobs in two separate tables
that were completed or parked.

The filename in column M on the FolderLogs tab and column A (for
completed) or column E (for parked) on the ReaderLogs tab is what
links the items together. When a job appears on the ReaderLog as
"completed", the completion date of the job is pulled over into the
FolderLog into Column V through a vlookup formula comparing the
filename:

=IF(ISNA(VLOOKUP(FolderLogs!M2,'ReaderLogs'!A:B,2,0)),"",IF(VLOOKUP
(FolderLogs!M2,'ReaderLogs'!A:B,2,0)=0,"",VLOOKUP(FolderLogs!
M2,'ReaderLogs'!A:B,2,0)))


Column Z is then updated with an if statement that identifies the
status of the folder depending on what fields have dates entered into
them using the following formula:
=IF(A2="","",IF(U2<>"","Completed",IF(Y2<>"","Dispatched",IF
(V2<>"","Parked",IF(K2<>"","Dispatched",IF
(J2<>"","Created","Pending"))))))


----------
Issue:
----------

My problem lies in that a folder with the exact same filename can
potentially be sent through the system twice, if that happens, my
vlookup formulas are going to retrieve the previous statuses since
they’ll be the first match, and jobs that are in progress will show
completed even though they haven’t been yet.

The good news is that once a job is completed, I don’t need to keep
the information separately on the ReaderLog tab, and I can remove
it. In order to remove it though, I need to update all row items on
the FolderLog tab that have a status of ‘completed’ to remove the
formulas.


The best I can come up with is needing to use a vb macro where if the
status column is calculating that the status in column Z =
“Completed”, the macro will select the entire row and copy/paste-
special to replace the formula with current values. After completing
this for the entire FolderLog tab, it should then go to the readerlog
tab and delete items that are completed (if column c = completed,
delete values in columns a& b AND if column J = completed, delete
values in columns E,F,G,H &I)

Alternatively, the macro might be also able to say if the calculation
in the date completed column (U) returns a value that is not blank,
(follow the same set of actions as above to replace the formulas with
values and delete values in the other sheet)

Thoughts? Ideas? I'm stumped.

 

[joel]

I wrote 2 seperate macros to make it easier to get the code working. Yo
always can combine them after you tested the code.

Sub ReplaceFolderLogs()


With Sheets("FolderLog")
Set c = .Columns("Z").Find(what:="Completed", _
lookat:=xlWhole, LookIn:=xlValues)
If c Is Nothing Then
MsgBox ("No completed items found")
Else
FirstAddr = c.Address
Do
c.EntireRow.Copy
c.EntireRow.PasteSpecial _
Paste:=xlPasteValues
Set c = .Columns("Z").FindNext(after:=c)
Loop While Not c Is Nothing And c.Address <> FirstAddr
End If
End With


End Sub
Sub Replacereaderlog()


With Sheets("readerlogg")
LastRow = .Cells.SpecialCells(xlCellTypeLastCell)
For RowCount = 1 To LastRow
.Range("A" & RowCount).ClearContents
.Range("B" & RowCount).ClearContents

If UCase(.Range("J" & RowCount)) = "COMPLETED" Then
.Range("E" & RowCount).ClearContents
.Range("F" & RowCount).ClearContents
.Range("G" & RowCount).ClearContents
.Range("H" & RowCount).ClearContents
.Range("I" & RowCount).ClearContents

End If
Next RowCount
End With

End Sub