Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help, Please
A workbook contains three worksheets: (1) "Server Delete List", (2) "Data Processing Sheet" and (3) "Loadlist Processing". The code is run from a control button on "Server Delete List" worksheet (Sheet 1). That code runs fine in processing data on the "Data Processing Sheet" (Sheet 2). On Sheet 3, the code deletes entire rows based on several criteria: duplicate numbers, cells containing the strings "Digibeta" and "Record". It runs fine in processing the duplicate number row delete function code on the "Loadlist Processing" worksheet (Sheet 3). But, it fails when trying to run the "Digibeta" and "Record" row delete code on Sheet 3. I should note that both the "Digibeta" and "Record" row delete code runs fine when run from a macro created on and run from Sheet 3. So, I suspect that I have omitted one or more references to Sheet 3 ("Loadlist Processing") in the code that runs from Sheet 1 for these two operations. But, I can't figure out what! Any suggestions would be most welcomed. Thanks and regards, PJF __________________________________________________ ____________________ Here is the relevant code: Sheets("LoadList Processing").Select '(Sheet 3) ActiveSheet.Range(Selection, "A1:A1000").Select Dim Col As Integer Dim r As Long Dim C As Range Dim N As Long Dim V As Variant Dim Rng As Range On Error GoTo EndMacro Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Col = ActiveCell.Column If Selection.Rows.Count 1 Then Set Rng = Selection Else Set Rng = ActiveSheet.UsedRange.Rows End If N = 0 For r = Rng.Rows.Count To 1 Step -1 V = Rng.Cells(r, 1).Value If Application.WorksheetFunction.CountIf(Rng.Columns( 1), V) 1 Then Rng.Rows(r).EntireRow.Delete N = N + 1 End If Next r EndMacro: Application.Calculation = xlCalculationAutomatic 'Comment: the code above works fine. The code below fails. Sheets("LoadList Processing").Select '(Sheet 3) Application.Goto Reference:="R1C2" ActiveSheet.Range(Selection, Selection.End(xlDown)).Select findstring = "Digibeta" Set b = Columns(ActiveCell.Column).Find(What:=findstring, LookAt:=xlWhole) While Not (b Is Nothing) b.EntireRow.Delete Set b = Columns(ActiveCell.Column).Find(What:=findstring, LookAt:=xlWhole) Wend Application.CutCopyMode = False Sheets("LoadList Processing").Select '(Sheet 3) Application.Goto Reference:="R1C3" ActiveSheet.Range(Selection, Selection.End(xlDown)).Select findstring = "Record" Set b = Columns(ActiveCell.Column).Find(What:=findstring, LookAt:=xlWhole) While Not (b Is Nothing) b.EntireRow.Delete Set b = Columns(ActiveCell.Column).Find(What:=findstring, LookAt:=xlWhole) Wend Application.CutCopyMode = False 'Comment: As noted in the message, all of this code works fine when run from a macro created on and run from Sheet 3. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help, Please
Sheets("LoadList Processing").Select '(Sheet 3)
ActiveSheet.Range(Selection, "A1:A1000").Select Dim Col As Integer Dim r As Long Dim C As Range Dim N As Long Dim V As Variant Dim Rng As Range dim rngSh3 as Range On Error GoTo EndMacro Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Col = ActiveCell.Column If Selection.Rows.Count 1 Then Set Rng = Selection Else Set Rng = ActiveSheet.UsedRange.Rows End If N = 0 For r = Rng.Rows.Count To 1 Step -1 V = Rng.Cells(r, 1).Value If Application.WorksheetFunction.CountIf(Rng.Columns( 1), V) 1 Then Rng.Rows(r).EntireRow.Delete N = N + 1 End If Next r EndMacro: Application.Calculation = xlCalculationAutomatic 'Comment: the code above works fine. The code below fails. With Sheets("LoadList Processing").Select '(Sheet 3) set rngSH3 = .Cells(1,2) findstring = "Digibeta" Set b = .Columns(rngSh3.column).Find(What:=findstring, _ LookAt:=xlWhole) While Not (b Is Nothing) b.EntireRow.Delete Set b = .Columns(rngSh3.Column).Find(What:=findstring, _ LookAt:=xlWhole) Wend set rngSh3:=.Cells(1,3) findstring = "Record" Set b = .Columns(rngSh3.column).Find(What:=findstring, _ LookAt:=xlWhole) While Not (b Is Nothing) b.EntireRow.Delete Set b = .Columns(rngSh3.column).Find(What:=findstring, _ LookAt:=xlWhole) Wend Application.CutCopyMode = False End With Unqualifed references to ranges in a sheet module refer to the sheet of the sheet module, not the activesheet. Regards, Tom Ogilvy PJF wrote in message ... A workbook contains three worksheets: (1) "Server Delete List", (2) "Data Processing Sheet" and (3) "Loadlist Processing". The code is run from a control button on "Server Delete List" worksheet (Sheet 1). That code runs fine in processing data on the "Data Processing Sheet" (Sheet 2). On Sheet 3, the code deletes entire rows based on several criteria: duplicate numbers, cells containing the strings "Digibeta" and "Record". It runs fine in processing the duplicate number row delete function code on the "Loadlist Processing" worksheet (Sheet 3). But, it fails when trying to run the "Digibeta" and "Record" row delete code on Sheet 3. I should note that both the "Digibeta" and "Record" row delete code runs fine when run from a macro created on and run from Sheet 3. So, I suspect that I have omitted one or more references to Sheet 3 ("Loadlist Processing") in the code that runs from Sheet 1 for these two operations. But, I can't figure out what! Any suggestions would be most welcomed. Thanks and regards, PJF __________________________________________________ ____________________ Here is the relevant code: Sheets("LoadList Processing").Select '(Sheet 3) ActiveSheet.Range(Selection, "A1:A1000").Select Dim Col As Integer Dim r As Long Dim C As Range Dim N As Long Dim V As Variant Dim Rng As Range On Error GoTo EndMacro Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Col = ActiveCell.Column If Selection.Rows.Count 1 Then Set Rng = Selection Else Set Rng = ActiveSheet.UsedRange.Rows End If N = 0 For r = Rng.Rows.Count To 1 Step -1 V = Rng.Cells(r, 1).Value If Application.WorksheetFunction.CountIf(Rng.Columns( 1), V) 1 Then Rng.Rows(r).EntireRow.Delete N = N + 1 End If Next r EndMacro: Application.Calculation = xlCalculationAutomatic 'Comment: the code above works fine. The code below fails. Sheets("LoadList Processing").Select '(Sheet 3) Application.Goto Reference:="R1C2" ActiveSheet.Range(Selection, Selection.End(xlDown)).Select findstring = "Digibeta" Set b = Columns(ActiveCell.Column).Find(What:=findstring, LookAt:=xlWhole) While Not (b Is Nothing) b.EntireRow.Delete Set b = Columns(ActiveCell.Column).Find(What:=findstring, LookAt:=xlWhole) Wend Application.CutCopyMode = False Sheets("LoadList Processing").Select '(Sheet 3) Application.Goto Reference:="R1C3" ActiveSheet.Range(Selection, Selection.End(xlDown)).Select findstring = "Record" Set b = Columns(ActiveCell.Column).Find(What:=findstring, LookAt:=xlWhole) While Not (b Is Nothing) b.EntireRow.Delete Set b = Columns(ActiveCell.Column).Find(What:=findstring, LookAt:=xlWhole) Wend Application.CutCopyMode = False 'Comment: As noted in the message, all of this code works fine when run from a macro created on and run from Sheet 3. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help, Please
Tom,
Thanks so much for your quick reply. Below is the code that I incorporated from you message. However, I do get a "Run-Time Error '424', Object Required" when stepping through the code that starts: With Sheets("LoadList Processing").Select '(Sheet 3) set rngSH3 = .Cells(1,2) Interestingly, when I run the code from the top up to the first instance of line that begins: With Sheets("LoadList Processing").Select '(Sheet 3) and as I step through the code (press F8), it first steps through the two lines of code above, then jumps up to the line with the code: Application.Calculation = xlCalculationAutomatic Then, steps through the same two lines of code above, then I get the Error 424 message. See anything I overlooked in implementing your suggested code? Thanks and regards, PJF __________________________________________________ _____________________ Dim Col As Integer Dim r As Long Dim C As Range Dim N As Long Dim V As Variant Dim Rng As Range Dim rngSh3 As Range On Error GoTo EndMacro Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Col = ActiveCell.Column If Selection.Rows.Count 1 Then Set Rng = Selection Else Set Rng = ActiveSheet.UsedRange.Rows End If N = 0 For r = Rng.Rows.Count To 1 Step -1 V = Rng.Cells(r, 1).Value If Application.WorksheetFunction.CountIf(Rng.Columns( 1), V) 1 Then Rng.Rows(r).EntireRow.Delete N = N + 1 End If Next r EndMacro: Application.Calculation = xlCalculationAutomatic 'Application.ScreenUpdating = False With Sheets("LoadList Processing").Select '(Sheet 3) Set rngSh3 = .Cells(1, 2) findstring = "Digibeta" Set b = .Columns(rngSh3.Column).Find(What:=findstring, _ LookAt:=xlWhole) While Not (b Is Nothing) b.EntireRow.Delete Set b = .Columns(rngSh3.Column).Find(What:=findstring, _ LookAt:=xlWhole) Wend With Sheets("LoadList Processing").Select '(Sheet 3) Set rngSh3 = .Cells(1, 3) findstring = "Record" Set b = .Columns(rngSh3.Column).Find(What:=findstring, _ LookAt:=xlWhole) While Not (b Is Nothing) b.EntireRow.Delete Set b = .Columns(rngSh3.Column).Find(What:=findstring, _ LookAt:=xlWhole) Wend Application.CutCopyMode = False End With "Tom Ogilvy" wrote in message ... Sheets("LoadList Processing").Select '(Sheet 3) ActiveSheet.Range(Selection, "A1:A1000").Select Dim Col As Integer Dim r As Long Dim C As Range Dim N As Long Dim V As Variant Dim Rng As Range dim rngSh3 as Range On Error GoTo EndMacro Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Col = ActiveCell.Column If Selection.Rows.Count 1 Then Set Rng = Selection Else Set Rng = ActiveSheet.UsedRange.Rows End If N = 0 For r = Rng.Rows.Count To 1 Step -1 V = Rng.Cells(r, 1).Value If Application.WorksheetFunction.CountIf(Rng.Columns( 1), V) 1 Then Rng.Rows(r).EntireRow.Delete N = N + 1 End If Next r EndMacro: Application.Calculation = xlCalculationAutomatic 'Comment: the code above works fine. The code below fails. With Sheets("LoadList Processing").Select '(Sheet 3) set rngSH3 = .Cells(1,2) findstring = "Digibeta" Set b = .Columns(rngSh3.column).Find(What:=findstring, _ LookAt:=xlWhole) While Not (b Is Nothing) b.EntireRow.Delete Set b = .Columns(rngSh3.Column).Find(What:=findstring, _ LookAt:=xlWhole) Wend set rngSh3:=.Cells(1,3) findstring = "Record" Set b = .Columns(rngSh3.column).Find(What:=findstring, _ LookAt:=xlWhole) While Not (b Is Nothing) b.EntireRow.Delete Set b = .Columns(rngSh3.column).Find(What:=findstring, _ LookAt:=xlWhole) Wend Application.CutCopyMode = False End With Unqualifed references to ranges in a sheet module refer to the sheet of the sheet module, not the activesheet. Regards, Tom Ogilvy PJF wrote in message ... A workbook contains three worksheets: (1) "Server Delete List", (2) "Data Processing Sheet" and (3) "Loadlist Processing". The code is run from a control button on "Server Delete List" worksheet (Sheet 1). That code runs fine in processing data on the "Data Processing Sheet" (Sheet 2). On Sheet 3, the code deletes entire rows based on several criteria: duplicate numbers, cells containing the strings "Digibeta" and "Record". It runs fine in processing the duplicate number row delete function code on the "Loadlist Processing" worksheet (Sheet 3). But, it fails when trying to run the "Digibeta" and "Record" row delete code on Sheet 3. I should note that both the "Digibeta" and "Record" row delete code runs fine when run from a macro created on and run from Sheet 3. So, I suspect that I have omitted one or more references to Sheet 3 ("Loadlist Processing") in the code that runs from Sheet 1 for these two operations. But, I can't figure out what! Any suggestions would be most welcomed. Thanks and regards, PJF __________________________________________________ ____________________ Here is the relevant code: Sheets("LoadList Processing").Select '(Sheet 3) ActiveSheet.Range(Selection, "A1:A1000").Select Dim Col As Integer Dim r As Long Dim C As Range Dim N As Long Dim V As Variant Dim Rng As Range On Error GoTo EndMacro Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Col = ActiveCell.Column If Selection.Rows.Count 1 Then Set Rng = Selection Else Set Rng = ActiveSheet.UsedRange.Rows End If N = 0 For r = Rng.Rows.Count To 1 Step -1 V = Rng.Cells(r, 1).Value If Application.WorksheetFunction.CountIf(Rng.Columns( 1), V) 1 Then Rng.Rows(r).EntireRow.Delete N = N + 1 End If Next r EndMacro: Application.Calculation = xlCalculationAutomatic 'Comment: the code above works fine. The code below fails. Sheets("LoadList Processing").Select '(Sheet 3) Application.Goto Reference:="R1C2" ActiveSheet.Range(Selection, Selection.End(xlDown)).Select findstring = "Digibeta" Set b = Columns(ActiveCell.Column).Find(What:=findstring, LookAt:=xlWhole) While Not (b Is Nothing) b.EntireRow.Delete Set b = Columns(ActiveCell.Column).Find(What:=findstring, LookAt:=xlWhole) Wend Application.CutCopyMode = False Sheets("LoadList Processing").Select '(Sheet 3) Application.Goto Reference:="R1C3" ActiveSheet.Range(Selection, Selection.End(xlDown)).Select findstring = "Record" Set b = Columns(ActiveCell.Column).Find(What:=findstring, LookAt:=xlWhole) While Not (b Is Nothing) b.EntireRow.Delete Set b = Columns(ActiveCell.Column).Find(What:=findstring, LookAt:=xlWhole) Wend Application.CutCopyMode = False 'Comment: As noted in the message, all of this code works fine when run from a macro created on and run from Sheet 3. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help, Please
With Sheets("LoadList Processing").Select '(Sheet 3)
Just trouble editing. The .Select should be gone With Sheets("LoadList Processing"). Regards, Tom Ogilvy Tom Ogilvy wrote in message ... Sheets("LoadList Processing").Select '(Sheet 3) ActiveSheet.Range(Selection, "A1:A1000").Select Dim Col As Integer Dim r As Long Dim C As Range Dim N As Long Dim V As Variant Dim Rng As Range dim rngSh3 as Range On Error GoTo EndMacro Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Col = ActiveCell.Column If Selection.Rows.Count 1 Then Set Rng = Selection Else Set Rng = ActiveSheet.UsedRange.Rows End If N = 0 For r = Rng.Rows.Count To 1 Step -1 V = Rng.Cells(r, 1).Value If Application.WorksheetFunction.CountIf(Rng.Columns( 1), V) 1 Then Rng.Rows(r).EntireRow.Delete N = N + 1 End If Next r EndMacro: Application.Calculation = xlCalculationAutomatic 'Comment: the code above works fine. The code below fails. With Sheets("LoadList Processing").Select '(Sheet 3) set rngSH3 = .Cells(1,2) findstring = "Digibeta" Set b = .Columns(rngSh3.column).Find(What:=findstring, _ LookAt:=xlWhole) While Not (b Is Nothing) b.EntireRow.Delete Set b = .Columns(rngSh3.Column).Find(What:=findstring, _ LookAt:=xlWhole) Wend set rngSh3:=.Cells(1,3) findstring = "Record" Set b = .Columns(rngSh3.column).Find(What:=findstring, _ LookAt:=xlWhole) While Not (b Is Nothing) b.EntireRow.Delete Set b = .Columns(rngSh3.column).Find(What:=findstring, _ LookAt:=xlWhole) Wend Application.CutCopyMode = False End With Unqualifed references to ranges in a sheet module refer to the sheet of the sheet module, not the activesheet. Regards, Tom Ogilvy PJF wrote in message ... A workbook contains three worksheets: (1) "Server Delete List", (2) "Data Processing Sheet" and (3) "Loadlist Processing". The code is run from a control button on "Server Delete List" worksheet (Sheet 1). That code runs fine in processing data on the "Data Processing Sheet" (Sheet 2). On Sheet 3, the code deletes entire rows based on several criteria: duplicate numbers, cells containing the strings "Digibeta" and "Record". It runs fine in processing the duplicate number row delete function code on the "Loadlist Processing" worksheet (Sheet 3). But, it fails when trying to run the "Digibeta" and "Record" row delete code on Sheet 3. I should note that both the "Digibeta" and "Record" row delete code runs fine when run from a macro created on and run from Sheet 3. So, I suspect that I have omitted one or more references to Sheet 3 ("Loadlist Processing") in the code that runs from Sheet 1 for these two operations. But, I can't figure out what! Any suggestions would be most welcomed. Thanks and regards, PJF __________________________________________________ ____________________ Here is the relevant code: Sheets("LoadList Processing").Select '(Sheet 3) ActiveSheet.Range(Selection, "A1:A1000").Select Dim Col As Integer Dim r As Long Dim C As Range Dim N As Long Dim V As Variant Dim Rng As Range On Error GoTo EndMacro Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Col = ActiveCell.Column If Selection.Rows.Count 1 Then Set Rng = Selection Else Set Rng = ActiveSheet.UsedRange.Rows End If N = 0 For r = Rng.Rows.Count To 1 Step -1 V = Rng.Cells(r, 1).Value If Application.WorksheetFunction.CountIf(Rng.Columns( 1), V) 1 Then Rng.Rows(r).EntireRow.Delete N = N + 1 End If Next r EndMacro: Application.Calculation = xlCalculationAutomatic 'Comment: the code above works fine. The code below fails. Sheets("LoadList Processing").Select '(Sheet 3) Application.Goto Reference:="R1C2" ActiveSheet.Range(Selection, Selection.End(xlDown)).Select findstring = "Digibeta" Set b = Columns(ActiveCell.Column).Find(What:=findstring, LookAt:=xlWhole) While Not (b Is Nothing) b.EntireRow.Delete Set b = Columns(ActiveCell.Column).Find(What:=findstring, LookAt:=xlWhole) Wend Application.CutCopyMode = False Sheets("LoadList Processing").Select '(Sheet 3) Application.Goto Reference:="R1C3" ActiveSheet.Range(Selection, Selection.End(xlDown)).Select findstring = "Record" Set b = Columns(ActiveCell.Column).Find(What:=findstring, LookAt:=xlWhole) While Not (b Is Nothing) b.EntireRow.Delete Set b = Columns(ActiveCell.Column).Find(What:=findstring, LookAt:=xlWhole) Wend Application.CutCopyMode = False 'Comment: As noted in the message, all of this code works fine when run from a macro created on and run from Sheet 3. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help, Please
as should the period at the end:
With Sheets("LoadList Processing") Regards, Tom Ogilvy Tom Ogilvy wrote in message ... With Sheets("LoadList Processing").Select '(Sheet 3) Just trouble editing. The .Select should be gone With Sheets("LoadList Processing"). Regards, Tom Ogilvy Tom Ogilvy wrote in message ... Sheets("LoadList Processing").Select '(Sheet 3) ActiveSheet.Range(Selection, "A1:A1000").Select Dim Col As Integer Dim r As Long Dim C As Range Dim N As Long Dim V As Variant Dim Rng As Range dim rngSh3 as Range On Error GoTo EndMacro Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Col = ActiveCell.Column If Selection.Rows.Count 1 Then Set Rng = Selection Else Set Rng = ActiveSheet.UsedRange.Rows End If N = 0 For r = Rng.Rows.Count To 1 Step -1 V = Rng.Cells(r, 1).Value If Application.WorksheetFunction.CountIf(Rng.Columns( 1), V) 1 Then Rng.Rows(r).EntireRow.Delete N = N + 1 End If Next r EndMacro: Application.Calculation = xlCalculationAutomatic 'Comment: the code above works fine. The code below fails. With Sheets("LoadList Processing").Select '(Sheet 3) set rngSH3 = .Cells(1,2) findstring = "Digibeta" Set b = .Columns(rngSh3.column).Find(What:=findstring, _ LookAt:=xlWhole) While Not (b Is Nothing) b.EntireRow.Delete Set b = .Columns(rngSh3.Column).Find(What:=findstring, _ LookAt:=xlWhole) Wend set rngSh3:=.Cells(1,3) findstring = "Record" Set b = .Columns(rngSh3.column).Find(What:=findstring, _ LookAt:=xlWhole) While Not (b Is Nothing) b.EntireRow.Delete Set b = .Columns(rngSh3.column).Find(What:=findstring, _ LookAt:=xlWhole) Wend Application.CutCopyMode = False End With Unqualifed references to ranges in a sheet module refer to the sheet of the sheet module, not the activesheet. Regards, Tom Ogilvy PJF wrote in message ... A workbook contains three worksheets: (1) "Server Delete List", (2) "Data Processing Sheet" and (3) "Loadlist Processing". The code is run from a control button on "Server Delete List" worksheet (Sheet 1). That code runs fine in processing data on the "Data Processing Sheet" (Sheet 2). On Sheet 3, the code deletes entire rows based on several criteria: duplicate numbers, cells containing the strings "Digibeta" and "Record". It runs fine in processing the duplicate number row delete function code on the "Loadlist Processing" worksheet (Sheet 3). But, it fails when trying to run the "Digibeta" and "Record" row delete code on Sheet 3. I should note that both the "Digibeta" and "Record" row delete code runs fine when run from a macro created on and run from Sheet 3. So, I suspect that I have omitted one or more references to Sheet 3 ("Loadlist Processing") in the code that runs from Sheet 1 for these two operations. But, I can't figure out what! Any suggestions would be most welcomed. Thanks and regards, PJF __________________________________________________ ____________________ Here is the relevant code: Sheets("LoadList Processing").Select '(Sheet 3) ActiveSheet.Range(Selection, "A1:A1000").Select Dim Col As Integer Dim r As Long Dim C As Range Dim N As Long Dim V As Variant Dim Rng As Range On Error GoTo EndMacro Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Col = ActiveCell.Column If Selection.Rows.Count 1 Then Set Rng = Selection Else Set Rng = ActiveSheet.UsedRange.Rows End If N = 0 For r = Rng.Rows.Count To 1 Step -1 V = Rng.Cells(r, 1).Value If Application.WorksheetFunction.CountIf(Rng.Columns( 1), V) 1 Then Rng.Rows(r).EntireRow.Delete N = N + 1 End If Next r EndMacro: Application.Calculation = xlCalculationAutomatic 'Comment: the code above works fine. The code below fails. Sheets("LoadList Processing").Select '(Sheet 3) Application.Goto Reference:="R1C2" ActiveSheet.Range(Selection, Selection.End(xlDown)).Select findstring = "Digibeta" Set b = Columns(ActiveCell.Column).Find(What:=findstring, LookAt:=xlWhole) While Not (b Is Nothing) b.EntireRow.Delete Set b = Columns(ActiveCell.Column).Find(What:=findstring, LookAt:=xlWhole) Wend Application.CutCopyMode = False Sheets("LoadList Processing").Select '(Sheet 3) Application.Goto Reference:="R1C3" ActiveSheet.Range(Selection, Selection.End(xlDown)).Select findstring = "Record" Set b = Columns(ActiveCell.Column).Find(What:=findstring, LookAt:=xlWhole) While Not (b Is Nothing) b.EntireRow.Delete Set b = Columns(ActiveCell.Column).Find(What:=findstring, LookAt:=xlWhole) Wend Application.CutCopyMode = False 'Comment: As noted in the message, all of this code works fine when run from a macro created on and run from Sheet 3. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help, Please
Tom,
That did the trick. Thanks for your patience and valuable help. Frankly, I don't know what I and many of us relative nubies to VBA would do without your willingness and that of your fellow experts to share your expertise. Kindest regards, PJF "Tom Ogilvy" wrote in message ... as should the period at the end: With Sheets("LoadList Processing") Regards, Tom Ogilvy Tom Ogilvy wrote in message ... With Sheets("LoadList Processing").Select '(Sheet 3) Just trouble editing. The .Select should be gone With Sheets("LoadList Processing"). Regards, Tom Ogilvy Tom Ogilvy wrote in message ... Sheets("LoadList Processing").Select '(Sheet 3) ActiveSheet.Range(Selection, "A1:A1000").Select Dim Col As Integer Dim r As Long Dim C As Range Dim N As Long Dim V As Variant Dim Rng As Range dim rngSh3 as Range On Error GoTo EndMacro Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Col = ActiveCell.Column If Selection.Rows.Count 1 Then Set Rng = Selection Else Set Rng = ActiveSheet.UsedRange.Rows End If N = 0 For r = Rng.Rows.Count To 1 Step -1 V = Rng.Cells(r, 1).Value If Application.WorksheetFunction.CountIf(Rng.Columns( 1), V) 1 Then Rng.Rows(r).EntireRow.Delete N = N + 1 End If Next r EndMacro: Application.Calculation = xlCalculationAutomatic 'Comment: the code above works fine. The code below fails. With Sheets("LoadList Processing").Select '(Sheet 3) set rngSH3 = .Cells(1,2) findstring = "Digibeta" Set b = .Columns(rngSh3.column).Find(What:=findstring, _ LookAt:=xlWhole) While Not (b Is Nothing) b.EntireRow.Delete Set b = .Columns(rngSh3.Column).Find(What:=findstring, _ LookAt:=xlWhole) Wend set rngSh3:=.Cells(1,3) findstring = "Record" Set b = .Columns(rngSh3.column).Find(What:=findstring, _ LookAt:=xlWhole) While Not (b Is Nothing) b.EntireRow.Delete Set b = .Columns(rngSh3.column).Find(What:=findstring, _ LookAt:=xlWhole) Wend Application.CutCopyMode = False End With Unqualifed references to ranges in a sheet module refer to the sheet of the sheet module, not the activesheet. Regards, Tom Ogilvy PJF wrote in message ... A workbook contains three worksheets: (1) "Server Delete List", (2) "Data Processing Sheet" and (3) "Loadlist Processing". The code is run from a control button on "Server Delete List" worksheet (Sheet 1). That code runs fine in processing data on the "Data Processing Sheet" (Sheet 2). On Sheet 3, the code deletes entire rows based on several criteria: duplicate numbers, cells containing the strings "Digibeta" and "Record". It runs fine in processing the duplicate number row delete function code on the "Loadlist Processing" worksheet (Sheet 3). But, it fails when trying to run the "Digibeta" and "Record" row delete code on Sheet 3. I should note that both the "Digibeta" and "Record" row delete code runs fine when run from a macro created on and run from Sheet 3. So, I suspect that I have omitted one or more references to Sheet 3 ("Loadlist Processing") in the code that runs from Sheet 1 for these two operations. But, I can't figure out what! Any suggestions would be most welcomed. Thanks and regards, PJF __________________________________________________ ____________________ Here is the relevant code: Sheets("LoadList Processing").Select '(Sheet 3) ActiveSheet.Range(Selection, "A1:A1000").Select Dim Col As Integer Dim r As Long Dim C As Range Dim N As Long Dim V As Variant Dim Rng As Range On Error GoTo EndMacro Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Col = ActiveCell.Column If Selection.Rows.Count 1 Then Set Rng = Selection Else Set Rng = ActiveSheet.UsedRange.Rows End If N = 0 For r = Rng.Rows.Count To 1 Step -1 V = Rng.Cells(r, 1).Value If Application.WorksheetFunction.CountIf(Rng.Columns( 1), V) 1 Then Rng.Rows(r).EntireRow.Delete N = N + 1 End If Next r EndMacro: Application.Calculation = xlCalculationAutomatic 'Comment: the code above works fine. The code below fails. Sheets("LoadList Processing").Select '(Sheet 3) Application.Goto Reference:="R1C2" ActiveSheet.Range(Selection, Selection.End(xlDown)).Select findstring = "Digibeta" Set b = Columns(ActiveCell.Column).Find(What:=findstring, LookAt:=xlWhole) While Not (b Is Nothing) b.EntireRow.Delete Set b = Columns(ActiveCell.Column).Find(What:=findstring, LookAt:=xlWhole) Wend Application.CutCopyMode = False Sheets("LoadList Processing").Select '(Sheet 3) Application.Goto Reference:="R1C3" ActiveSheet.Range(Selection, Selection.End(xlDown)).Select findstring = "Record" Set b = Columns(ActiveCell.Column).Find(What:=findstring, LookAt:=xlWhole) While Not (b Is Nothing) b.EntireRow.Delete Set b = Columns(ActiveCell.Column).Find(What:=findstring, LookAt:=xlWhole) Wend Application.CutCopyMode = False 'Comment: As noted in the message, all of this code works fine when run from a macro created on and run from Sheet 3. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
need help to update macro to office 2007 macro enabled workbook | Excel Discussion (Misc queries) | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) |