If... Then with multiple actions; not working!

M

mcairtime

New Member
Joined
Aug 26, 2011
Messages
2
Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p> </o:p>
I am trying to get the following code to compare two ranges. If they are the same then the cell "y" is to be deleted, but I also want to delete the two adjacent cells y.offset(0, 1) and y.offset(0, 2). When I try to do any of these individually it works but when I try and follow the Then statement with instructions to delete all it doesn't work. The relevant section of code is below with the complete code at the bottom for reference.<o:p></o:p>
<o:p></o:p>
Tabledim = Application.CountA(Sheets("Main").Range("A1:A1000"))<o:p></o:p>
Dim CompareRange As Variant, x As Variant, y As Variant<o:p></o:p>
Dim a As Integer<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
For i = 2 To Tabledim<o:p></o:p>
If Cells(i, 2) = "Pre-Starting" Then<o:p></o:p>
Set CompareRange = Sheets("All Guides").Range("A3:A500")<o:p></o:p>
Sheets("Main").Cells(i, 1).Select<o:p></o:p>
For Each x In Selection<o:p></o:p>
For Each y In CompareRange<o:p></o:p>
If x = y Then y.Delete<o:p></o:p>
Next y<o:p></o:p>
Next x<o:p></o:p>
End If<o:p></o:p>
Next i<o:p></o:p>
<o:p> </o:p>
When I try to use<o:p></o:p>
<o:p> </o:p>
If x = y Then y.Delete: y.Offset(0, 1).Delete: y.Offset(0, 2).Delete<o:p></o:p>
<o:p> </o:p>
I get the message Error 424: An object is required, with ": y.Offset(0, 1).Delete" highlighted in the code.<o:p></o:p>
<o:p> </o:p>
When I try to use<o:p></o:p>
<o:p> </o:p>
If x = y Then <o:p></o:p>
y.Delete <o:p></o:p>
y.Offset(0, 1).Delete <o:p></o:p>
y.Offset(0, 2).Delete<o:p></o:p>
<o:p> </o:p>
I get the message Compilation Error: Next without For, with the "next" of "next y" highlighted.<o:p></o:p>
<o:p> </o:p>
Any help solving this would be much appreciated. I'm pretty new to this so it may be something stupid. Thanks very much.<o:p></o:p>
<o:p> </o:p>
Calum<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
Sub FindGuides()'<o:p></o:p>
<o:p> </o:p>
Sheets("Pre-Starting").Cells.ClearContents<o:p></o:p>
<o:p></o:p>
Sheets("Starting").Cells.ClearContents<o:p></o:p>
<o:p></o:p>
Sheets("turning").Cells.ClearContents<o:p></o:p>
<o:p></o:p>
Sheets("Vantage").Cells.ClearContents<o:p></o:p>
<o:p></o:p>
Sheets("Power").Cells.ClearContents <o:p></o:p>
<o:p></o:p>
Sheets("Maintenance").Range("A1:AD500").Copy Destination:=Sheets("All Guides").Range("A1:AD500")<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
Tabledim = Application.CountA(Sheets("Main").Range("A1:A1000"))<o:p></o:p>
Dim CompareRange As Variant, x As Variant, y As Variant<o:p></o:p>
Dim a As Integer<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
For i = 2 To Tabledim<o:p></o:p>
If Cells(i, 2) = "Pre-Starting" Then<o:p></o:p>
Set CompareRange = Sheets("All Guides").Range("A3:A500")<o:p></o:p>
Sheets("Main").Cells(i, 1).Select<o:p></o:p>
For Each x In Selection<o:p></o:p>
For Each y In CompareRange<o:p></o:p>
If x = y Then y.Delete<o:p></o:p>
Next y<o:p></o:p>
Next x<o:p></o:p>
<o:p></o:p>
ElseIf Cells(i, 2) = "Starting" Then<o:p></o:p>
Set CompareRange = Sheets("All Guides").Range("G3:G500")<o:p></o:p>
Sheets("Main").Cells(i, 1).Select<o:p></o:p>
For Each x In Selection<o:p></o:p>
For Each y In CompareRange<o:p></o:p>
If x = y Then y.Delete<o:p></o:p>
Next y<o:p></o:p>
Next x<o:p></o:p>
<o:p></o:p>
ElseIf Cells(i, 2) = "turning" Then<o:p></o:p>
Set CompareRange = Sheets("All Guides").Range("M3:M500")<o:p></o:p>
Sheets("Main").Cells(i, 1).Select<o:p></o:p>
For Each x In Selection<o:p></o:p>
For Each y In CompareRange<o:p></o:p>
If x = y Then y.Delete<o:p></o:p>
Next y<o:p></o:p>
Next x<o:p></o:p>
<o:p></o:p>
ElseIf Cells(i, 2) = "Vantage" Then<o:p></o:p>
Set CompareRange = Sheets("All Guides").Range("S3:S500")<o:p></o:p>
Sheets("Main").Cells(i, 1).Select<o:p></o:p>
For Each x In Selection<o:p></o:p>
For Each y In CompareRange<o:p></o:p>
If x = y Then y.Delete<o:p></o:p>
Next y<o:p></o:p>
Next x <o:p></o:p>
<o:p> </o:p>
ElseIf Cells(i, 2) = "Power" Then<o:p></o:p>
Set CompareRange = Sheets("All Guides").Range("Y3:Y500")<o:p></o:p>
Sheets("Main").Cells(i, 1).Select<o:p></o:p>
For Each x In Selection<o:p></o:p>
For Each y In CompareRange<o:p></o:p>
If x = y Then y.Delete<o:p></o:p>
Next y<o:p></o:p>
Next x<o:p></o:p>
<o:p></o:p>
End If<o:p></o:p>
Next i<o:p></o:p>
<o:p></o:p>
Dim Atabledim As Integer<o:p></o:p>
Atabledim = Application.CountA(Sheets("All Guides").Range("A3:A500"))<o:p></o:p>
Sheets("All Guides").Activate<o:p></o:p>
ActiveSheet.Range(Cells(3, 1), Cells(Atabledim + 2, 3)).Select<o:p></o:p>
Selection.Copy<o:p></o:p>
Sheets("Pre-Starting").Activate<o:p></o:p>
ActiveSheet.Range("A1").Select<o:p></o:p>
ActiveSheet.Paste<o:p></o:p>
<o:p></o:p>
Dim Btabledim As Integer<o:p></o:p>
Btabledim = Application.CountA(Sheets("All Guides").Range("G3:G500"))<o:p></o:p>
Sheets("All Guides").Activate<o:p></o:p>
ActiveSheet.Range(Cells(3, 7), Cells(Btabledim + 2, 9)).Select<o:p></o:p>
Selection.Copy<o:p></o:p>
Sheets("Starting").Activate<o:p></o:p>
ActiveSheet.Range("A1").Select<o:p></o:p>
ActiveSheet.Paste<o:p></o:p>
<o:p></o:p>
Dim Ctabledim As Integer<o:p></o:p>
Ctabledim = Application.CountA(Sheets("All Guides").Range("M3:M500"))<o:p></o:p>
Sheets("All Guides").Activate<o:p></o:p>
ActiveSheet.Range(Cells(3, 13), Cells(Ctabledim + 2, 15)).Select<o:p></o:p>
Selection.Copy<o:p></o:p>
Sheets("Turning").Activate<o:p></o:p>
ActiveSheet.Range("A1").Select<o:p></o:p>
ActiveSheet.Paste<o:p></o:p>
<o:p></o:p>
Dim Dtabledim As Integer<o:p></o:p>
Dtabledim = Application.CountA(Sheets("All Guides").Range("S3:S500"))<o:p></o:p>
Sheets("All Guides").Activate<o:p></o:p>
ActiveSheet.Range(Cells(3, 19), Cells(Dtabledim + 2, 21)).Select<o:p></o:p>
Selection.Copy<o:p></o:p>
Sheets("Vantage").Activate<o:p></o:p>
ActiveSheet.Range("A1").Select<o:p></o:p>
ActiveSheet.Paste<o:p></o:p>
<o:p></o:p>
Dim Etabledim As Integer<o:p></o:p>
Etabledim = Application.CountA(Sheets("All Guides").Range("Y3:Y500"))<o:p></o:p>
Sheets("All Guides").Activate<o:p></o:p>
ActiveSheet.Range(Cells(3, 25), Cells(Etabledim + 2, 27)).Select<o:p></o:p>
Selection.Copy<o:p></o:p>
Sheets("Power").Activate<o:p></o:p>
ActiveSheet.Range("A1").Select<o:p></o:p>
ActiveSheet.Paste<o:p></o:p>
<o:p></o:p>
Sheets("Main").Cells.ClearContents<o:p></o:p>
<o:p></o:p>
Sheets("Main").Range("A1") = "Paste your register here ya fanny!"<o:p></o:p>
With ActiveCell.Characters(Start:=1, Length:=34).Font<o:p></o:p>
.Name = "Arial"<o:p></o:p>
.FontStyle = "Negrita"<o:p></o:p>
.Size = 10<o:p></o:p>
.Strikethrough = False<o:p></o:p>
.Superscript = False<o:p></o:p>
.Subscript = False<o:p></o:p>
.OutlineFont = False<o:p></o:p>
.Shadow = False<o:p></o:p>
.Underline = xlUnderlineStyleNone<o:p></o:p>
.ColorIndex = 1<o:p></o:p>
End With<o:p></o:p>
Sheets("Main").Activate<o:p></o:p>
ActiveSheet.Range("A1").Select

End Sub
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
To delete the cells. At the moment the rest of the code counts the cells that are left then copies the cells across, although i could probably clear the cells and use an IF ISEMPTY or something.

thanks
 
Upvote 0
If you want to delete, maybe this (try it on a test-workbook)

If x = y Then <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
y.Resize(, 3).Delete
End If


M.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Splitting up an excel file to multiple tabs by "visible" rows.
2
Replies
11
Views
241
BasicUserWithExp
B
A
  • Solved
Could not set the RowSource property. Invalid property value. Error 380
2
Replies
16
Views
265
RoryA
RoryA
rjplante
  • Question Question
VBA to extract users name from outlook
Replies
2
Views
1K
Jaffirahamed
J
D
  • Question Question
Print Multiple invoices in one PDF File
Replies
1
Views
337
John_w
J
S
  • Question Question
Code path
2
Replies
10
Views
290
Jeffrey Mahoney
Jeffrey Mahoney

Forum statistics

Threads
1,224,600
Messages
6,179,833
Members
452,947
Latest member
Gerry_F

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top