Excel 2003 - Clean Up Formula Error Values and other

[frank_m]

I need vba code to clean up some columns, formulas and values. I've attached a workbook in an effort to make my desired result self explanatory and to supply data and format.

I apologize but I really don't know where to start. Speed is of some importance as there are more than 20,000 records and growing at a rate of about 5,000 per year.

Thanks

Edit: Out of 20,000 there will be less than one dozen that need the cleanup for Lot Charge

[frank_m]

The result "Lot" and "error" that I have asked for in Column P will only occur about a dozen or less times out of 20,000. - The vast majority will qualify for "ea" to be filled in column P, but I'm fine having that left out of the procedure to save processing time.

Thanks

[Bob Phillips]

Public Sub ProcessData()
Dim Lastrow As Long
Dim i As Long
Dim rng As Range
Dim cell As Range

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
Set rng = .Range("A1").Resize(Lastrow, 16)
rng.AutoFilter field:=8, Criteria1:="#VALUE!"
On Error Resume Next
Set rng = rng.Columns(8).SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If Not rng Is Nothing Then

For Each cell In rng

If LCase(Left(cell.Offset(0, -5).Value, 3)) = "lot" Then

cell.FormulaR1C1 = "=SUBSTITUTE(RC[-5],""Lot"","""")"
End If
Next cell
End If

.Range("A1").Resize(Lastrow, 16).AutoFilter
.Columns("P").NumberFormat = "General"
.Range("P2").Resize(Lastrow - 1).Formula = "=IF(ISERROR(H2),H2,IF(LEFT(C2,3)=""Lot"",""Lot"",""ea""))"
End With

Application.ScreenUpdating = True
End Sub

[frank_m]

Hi xld

your code is very close to what I'm looking to accomplish. Thanks so much for your time as I know this is not simple.

This site was giving me an account suspended message for about 6 or 8 hours which puzzled me so I did my best to make adjustments to end up with what I'm hoping for. (Unfortunately I fell just a little short)

You'll see on the Revised attachment that I added an additional 14 rows above the header and six columns to the left side causing several changes to be necessary.

If the amount in the Price Column (Column i) is prefixed with any other non-numeric value other than Lot or Lot$, I need the word "Error" in the far right column V (Column 22). Otherwise the Word "Lot" or "ea"

When clicking the Process button in the attached workbook the result after running it once gives me the Word "Lot" or "Error" and clears the contents in the amount column (Column N), which is what I want to happen, but I have to run it a second time to get the word "ea" filled in to the appropriate places in Column 22. Also after the second run it is incorrectly replacing the word "Error" with "ea"

Feel free to ditch my version of code.
hopefully both it and the revised attachment will serve to demonstrate what I'm hoping for.
Public Sub ProcessData()
Dim Lastrow As Long
Dim i As Long
Dim rng As Range
Dim cell As Range

Application.ScreenUpdating = False

With ActiveSheet
.Unprotect
Lastrow = .Cells(.Rows.Count, "N").End(xlUp).Row

Set rng = .Range("A15").Resize(Lastrow, 22)
rng.AutoFilter Field:=14, Criteria1:="#VALUE!"
On Error Resume Next

Set rng = Range("N16:N" & Lastrow).SpecialCells(xlCellTypeVisible)

.Columns("V").NumberFormat = "General"

.Range("V16:V" & Lastrow).Formula = _
"=IF(ISERROR(N16),N16,IF(LEFT(I16,3)=""Lot"",""Lot"",""ea""))"
End With

If Not rng Is Nothing Then
For Each cell In rng
If LCase(Left(cell.Offset(0, -5).Value, 3)) = "lot" Then
cell.FormulaR1C1 = _
"=SUBSTITUTE(SUBSTITUTE(UPPER(RC[-5]),""LOT"",""""),""$"","""")"
cell.Value = cell.Value 'replace the formula with the actual value
Else
If cell.Offset(0, 8).Text = "#VALUE!" Then
cell.Offset(0, 8).Value = "Error"
cell.ClearContents 'Clear(Col N)because price Prefixed with something other than "Lot"
End If
End If
Next cell
End If

Selection.AutoFilter Field:=14

Application.ScreenUpdating = True
End Sub

[frank_m]

Forgot to mention that I need the values in Column N to be available for summing in the status bar when selected.

The version of code that I posted does allow for that by clearing the #value! message.

[frank_m]

Edit: Never mind, I found the error in my ways.
Now it's working after I changed Column B to Column I (as Column B did not have any data)
Lastrow = .Cells(.Rows.Count, "I").End(xlUp).Row
Hi xld

Would you be so kind as to help me adapt your code to the fact that I added 6 more columns to the left and 14 more rows to the top of the sheet?
In my feeble attempt to adjust it shown below I am getting the following error:
Application or Object defined error on the command marked in Red below.

Thanks

Public Sub ProcessData()
Dim Lastrow As Long
Dim i As Long
Dim rng As Range
Dim cell As Range

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
Set rng = .Range("A1").Resize(Lastrow, 16)
rng.AutoFilter field:=14, Criteria1:="#VALUE!" '<-- I changed this from 8 to 14
On Error Resume Next
Set rng = rng.Columns(14).SpecialCells(xlCellTypeVisible)'<-- I changed this from 8 to 14
On Error GoTo 0

If Not rng Is Nothing Then

For Each cell In rng

If cell.Row > 15 Then '<-- I added this

If LCase(Left(cell.Offset(0, -5).Value, 3)) = "lot" Then

cell.FormulaR1C1 = "=SUBSTITUTE(RC[-5],""Lot"","""")"
End If
End If'<-- I added this
Next cell

End If

.Range("A15").Resize(Lastrow, 22).AutoFilter '<-- I changed this from A1 to A15
.Columns("V").NumberFormat = "General" '<-- I changed this from P to V
.Range("V2").Resize(Lastrow - 1).Formula = "=IF(ISERROR(N2),N2,IF(LEFT(I16,3)=""Lot"",""Lot"",""ea""))"

End With

Application.ScreenUpdating = True
End Sub

[frank_m]

One last question. I tweaked the code so that "#VALUE!" is cleared after the process and the word Error is added appropriately in Column V
Do you spot any flaws in how I accomplished that?
I attached a revised workbook to demonstrate that it seems to work ok as long as my summing formula is present in Column N before running the code (which it always is)

Thanks
Public Sub ProcessData()
Dim Lastrow As Long
Dim i As Long
Dim rng As Range
Dim cell As Range

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "I").End(xlUp).Row
Set rng = .Range("A1").Resize(Lastrow, 16)
rng.AutoFilter field:=14, Criteria1:="#VALUE!"
On Error Resume Next
Set rng = rng.Columns(14).SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If Not rng Is Nothing Then ' Moved the next two command's from after the loop to before
.Columns("V").NumberFormat = "General"
.Range("V2").Resize(Lastrow - 1).Formula = "=IF(ISERROR(N2),N2,IF(LEFT(I16,3)=""Lot"",""Lot"",""ea""))"
For Each cell In rng

If cell.Row > 15 Then

If LCase(Left(cell.Offset(0, -5).Value, 3)) = "lot" Then

cell.FormulaR1C1 = "=SUBSTITUTE(RC[-5],""Lot"","""")"
cell.Value = cell.Value'-Added because prefer having the value instead of formula
End If
End If

' Added this if condition
If cell.Text = "#VALUE!" Then
cell.Offset(0, 8).Value = "Error"
cell.ClearContents
End If

Next cell

End If

.Range("A15").Resize(Lastrow, 22).AutoFilter

End With

Application.ScreenUpdating = True
End Sub