View Full Version : Excel 2003 - Clean Up Formula Error Values and other

frank_m

01-13-2011, 10:46 PM

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

01-13-2011, 11:25 PM

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

01-14-2011, 02:23 AM

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

01-15-2011, 07:57 AM

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

01-15-2011, 08:27 AM

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

01-23-2011, 04:17 AM

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

01-23-2011, 05:02 AM

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

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.