Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
run-time error 1004
Hello... I have some clunky VB code that runs fine if I "run to cursor" in
stages, but if I try to run it complete by itself I get the following run-time error: Method 'Union' of object '_Global' failed. After importing delimited data and splitting it to columns, I'm using VB to create worksheet formulas to identify rows I want deleted, and then using VB to delete the rows. Since the formulas get screwed up after the first round of deletes, I am putting them in one at a time and running the delete scenario after each. The second "create-formula/row.delete" scenario is the one that is causing the error, though it runs fine running in stages: --- Set newdelRng = Union(rCell, newdelRng) --- I know this is not pretty code, and what I am trying to do could probably be accomplished much easier by a smarter author, but this is what I've got... any help? Sub Sats2MBS() Application.Calculation = xlAutomatic ' Deletes top 3 extraneous rows, splits remaining delimited text to columns Rows("1:3").Select Range("A3").Activate Selection.Delete Shift:=xlUp Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), Array(5, 2), Array(9, 2), Array(30, 2), Array(46, 2), _ Array(62, 1), Array(73, 1), Array(84, 2), Array(88, 1), Array(90, 1)), _ TrailingMinusNumbers:=True ' Installs "Blank Rows" formula in "R" Range("R1").Select ActiveCell.FormulaR1C1 = _ "=IF(COUNTA(R1C1:R50C1)<1,"""",IF(COUNTA(RC[-17]:RC[-5])<1,""DELETE"",""""))" Range("R1:R50000").Select Selection.FillDown ' Copies and Pastes values for entire sheet to eliminate formulas Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ' Sorts by column R so row removal sequence runs faster Cells.Select Selection.Sort Key1:=Range("r2"), Order1:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _ xlSortNormal ' Deletes empty rows based on formula in "R" Dim rng As Range Dim rCell As Range Dim delRng As Range Dim WB As Workbook Dim SH As Worksheet Dim CalcMode As Long Set WB = ActiveWorkbook Set SH = WB.Sheets("X") Set rng = Intersect(SH.UsedRange, SH.Columns("R:R")) On Error GoTo XIT With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With For Each rCell In rng.Cells If rCell.Value = "DELETE" Then If delRng Is Nothing Then Set delRng = rCell Else Set delRng = Union(rCell, delRng) End If End If Next rCell If Not delRng Is Nothing Then delRng.EntireRow.Delete End If XIT: With Application .Calculation = CalcMode .ScreenUpdating = True End With Application.Calculation = xlAutomatic ' Installs "Store Comparison" formulas in "R" Range("r1").Select ActiveCell.FormulaR1C1 = _ "=IF(COUNTA(R1C1:R50C1)<1,"""",IF(AND(R[-1]C[-5]=1,RC[-5]<1,ISBLANK(R[-1]C[-4])),"""",""DELETE""))" Range("r2").Select ActiveCell.FormulaR1C1 = _ "=IF(COUNTA(R1C1:R50C1)<1,"""",IF(AND(R[-1]C[-5]=1,RC[-5]<1,ISBLANK(R[-1]C[-4])),"""",""DELETE""))" Range("r2:r50000").Select Selection.FillDown ' Copies and Pastes values for entire sheet to eliminate formulas Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ' Sorts by column R so row removal sequence runs faster Cells.Select Selection.Sort Key1:=Range("r2"), Order1:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _ xlSortNormal ' Deletes rows based on formula in "R" Dim newdelRng As Range Set WB = ActiveWorkbook Set SH = WB.Sheets("X") Set rng = Intersect(SH.UsedRange, SH.Columns("R:R")) On Error GoTo XIT With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With For Each rCell In rng.Cells If rCell.Value = "DELETE" Then If newdelRng Is Nothing Then Set newdelRng = rCell Else Set newdelRng = Union(rCell, newdelRng) End If End If Next rCell If Not newdelRng Is Nothing Then newdelRng.EntireRow.Delete End If Application.Calculation = xlAutomatic ' Installs "Blank $" formula in "R" Range("r1").Select ActiveCell.FormulaR1C1 = _ "=IF(isblank(rc[-8]),""DELETE"","""")" Range("r1:r50000").Select Selection.FillDown ' Copies and Pastes values for entire sheet to eliminate formulas Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ' Sorts by column R so row removal sequence runs faster Cells.Select Selection.Sort Key1:=Range("r2"), Order1:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _ xlSortNormal ' Deletes rows based on formula in "R" Dim lastdelRng As Range Set WB = ActiveWorkbook Set SH = WB.Sheets("X") Set rng = Intersect(SH.UsedRange, SH.Columns("R:R")) On Error GoTo XIT With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With For Each rCell In rng.Cells If rCell.Value = "DELETE" Then If lastdelRng Is Nothing Then Set lastdelRng = rCell Else Set lastdelRng = Union(rCell, lastdelRng) End If End If Next rCell If Not lastdelRng Is Nothing Then lastdelRng.EntireRow.Delete End If ' Copies all, calls template with formulas, pastes values into template Dim FirstCell As Range Dim LastCell As Range If Not IsEmpty(Range("B1")) Then Set FirstCell = Range("A1") Else Set FirstCell = Range("A1").End(xlDown) End If Set LastCell = Cells(Rows.Count, "B").End(xlUp) Range(FirstCell, LastCell).EntireRow.Copy Workbooks.Add Template:="C:\Documents and Settings\kellyh\Desktop\UPC Reports\Format.UPC.sats.xlt" Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=True, Transpose:=False End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
run-time error 1004
Try a technique like this:
Step through the code to verify that it works the way you want it. '---------------------------------------------------------------------- 'rngData is a column of cells in a list of data, 'not including the header. Sub DeleteMarkedRows(rngData As Range) Dim rngCell As Range Dim rngMarkedCells As Range 'Copy and paste special to eliminate the formulas. 'They should now contain constants (i.e. "Delete", etc.), 'without having to do an Application.Calculate statement. With rngData .Copy .PasteSpecial Paste:=xlPasteValues End With Application.CutCopyMode = False 'Now clear the cells that have a non-blank string in them. For Each rngCell In rngData With rngCell If .Value = "" Then .ClearContents End With Next rngCell 'Now locate all of the marked cells, and delete only those rows. Set rngMarkedCells = rngData.SpecialCells(xlCellTypeConstants) rngMarkedCells.EntireRow.Delete End Sub -- Regards, Bill Renaud |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
run-time error 1004
(Untested): You could also use AutoFilter to show only rows that have
"Delete" (or whatever your formula inserted) in them. Then use: Set rngMarkedCells = rngData.SpecialCells(xlCellTypeVisible) You might stilll have to Copy and PasteSpecial to eliminate the formulas before doing the AutoFilter method, to prevent the cell values from changing. -- Regards, Bill Renaud |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
run-time error 1004
Thanks for the suggestion Bill... This doesn't seem to work for me, probably
because I don't know how to properly insert your code. But I think I need to consolidate some of what I'm doing anyway. I think I have figured out a better way, but now I need to know something else, if you don't mind continuing with this: How do I "fill down" a formula in a column based not on an absolute row range but on the last used row of a different column? I've tried every syntax I can think of but can't make it work. Basically, I want to fill down from "O1", with the last row being equivalent to the last used row in "A". tia, kelli "Bill Renaud" wrote: Try a technique like this: Step through the code to verify that it works the way you want it. '---------------------------------------------------------------------- 'rngData is a column of cells in a list of data, 'not including the header. Sub DeleteMarkedRows(rngData As Range) Dim rngCell As Range Dim rngMarkedCells As Range 'Copy and paste special to eliminate the formulas. 'They should now contain constants (i.e. "Delete", etc.), 'without having to do an Application.Calculate statement. With rngData .Copy .PasteSpecial Paste:=xlPasteValues End With Application.CutCopyMode = False 'Now clear the cells that have a non-blank string in them. For Each rngCell In rngData With rngCell If .Value = "" Then .ClearContents End With Next rngCell 'Now locate all of the marked cells, and delete only those rows. Set rngMarkedCells = rngData.SpecialCells(xlCellTypeConstants) rngMarkedCells.EntireRow.Delete End Sub -- Regards, Bill Renaud |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
run-time error 1004
I don't know why I didn't think of the filtering! I used this and it seems
to work fine, automatically undoing the filter after delete and leaving only the desired rows: Cells.Select Selection.AutoFilter Selection.AutoFilter Field:=15, Criteria1:="DELETE" Selection.Delete Per my reply after your filtering suggestion though, I am still trying to eliminate the need for "over-estimating" the number of rows to down-fill the formulas, making it be relative to the used space. thanks!! kelli "Bill Renaud" wrote: (Untested): You could also use AutoFilter to show only rows that have "Delete" (or whatever your formula inserted) in them. Then use: Set rngMarkedCells = rngData.SpecialCells(xlCellTypeVisible) You might stilll have to Copy and PasteSpecial to eliminate the formulas before doing the AutoFilter method, to prevent the cell values from changing. -- Regards, Bill Renaud |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
run-time error 1004
I'm glad this appears to be working for you, but I am always a little leery
of using code like: Cells.Select Selection.AutoFilter Selection.AutoFilter Field:=15, Criteria1:="DELETE" Selection.Delete as you can't always rely on what the Selection might be after doing several operations, especially since your first line selects all cells on the worksheet. This technique might cause all of your data to disappear someday! Be very careful about this! -- Regards, Bill Renaud |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
run-time error 1004 (How to FIll Down a Formula in a List)
Kelli wrote:
<<How do I "fill down" a formula in a column based not on an absolute row range but on the last used row of a different column? I've tried every syntax I can think of but can't make it work. Basically, I want to fill down from "O1", with the last row being equivalent to the last used row in "A". (Normally, you would have column labels (headers) in row 1, so you would actually enter the formula in O2, then fill down from there.) But, the simplest thing you can use would be something like the following (untested): Dim LastRow As Long LastRow = ActiveSheet.UsedRange.Rows.Count 'assuming the data starts in row 1. Range("O1:O" & LastRow).Formula = "=A1+B1/C1" 'or whatever the formula is. -- Regards, Bill Renaud |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
run-time error 1004 (How to FIll Down a Formula in a List)
Hmm... I can't seem to get this to work either. I tried to Set LastRow, and
get Object required error. Do I just do With LastRow = ...? There are no headers yet (they come later). Am I missing something on the syntax for the 'LastRow = ActiveSheet.UsedRange.Rows.Count' line? thanks, kelli "Bill Renaud" wrote: Kelli wrote: <<How do I "fill down" a formula in a column based not on an absolute row range but on the last used row of a different column? I've tried every syntax I can think of but can't make it work. Basically, I want to fill down from "O1", with the last row being equivalent to the last used row in "A". (Normally, you would have column labels (headers) in row 1, so you would actually enter the formula in O2, then fill down from there.) But, the simplest thing you can use would be something like the following (untested): Dim LastRow As Long LastRow = ActiveSheet.UsedRange.Rows.Count 'assuming the data starts in row 1. Range("O1:O" & LastRow).Formula = "=A1+B1/C1" 'or whatever the formula is. -- Regards, Bill Renaud |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
run-time error 1004 (How to FIll Down a Formula in a List)
Hi Kelli,
No, you are not missing anything on the line where "LastRow = ActiveSheet.UsedRange.Rows.Count". Use the code as given. Here is the complete routine, as I tested it. LastRow is a Long data type (a long form of integer). It is not an object variable, so it does not need to be "Set". If you step through the code (use the <F8 key) and watch the Locals window, you will see LastRow take on the value of how many rows you currently have on your worksheet. The next line of code will then fill in the formula in column $O from row 1 down to the last row. I removed the comments I had to prevent them from causing line wrap problems when you cut and paste the code from the newsgroup into a code window in Excel. Public Sub FillColumnO() Dim LastRow As Long LastRow = ActiveSheet.UsedRange.Rows.Count Range("O1:O" & LastRow).Formula = "=A1+B1/C1" End Sub If you had an object variable, then you would use "Set" as in the following example. Try this routine also, just to see how an object variable works. Public Sub GetList() Dim rngList As Range Set rngList = ActiveSheet.UsedRange rngList.Select End Sub -- Regards, Bill Renaud |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
run time error 1004 general odbc error excel 2003 vba | Excel Programming | |||
Run Time Error 1004: Application or Object Defined Error | Excel Programming | |||
Run Time 1004 Error: Application or Object Difine Error | Excel Programming | |||
run-time error '1004': Application-defined or object-deifined error | Excel Programming | |||
Run time error '1004': Generaol ODBC error | Excel Programming |