Compare a cell to a range then loop to compare another cell to the same range

[Shmevan11]

I'm trying to write a script that will check the contents of a cell and if a match is found it deletes the line containing the original cell.

Here's what I have so far:

Sub test()

Dim x As Long
x = 2
Dim format As Worksheet: Set format = Sheets("Formatting")
Dim pending As Worksheet: Set pending = Sheets("Pending")
Dim line As Range: Set line = ActiveWorkbook.format.Range(Cells(x, 1), Cells(x, 11))
Dim rng As Range: Set rng = ActiveWorkbook.pending.Range("A2", Range("A2").End(xlDown))
Dim txt As Range: Set txt = ActiveWorkbook.format.Range(format.Cells(x, 1))

Do While Sheets("Formatting").Cells(x, 1).Value <> ""

If Intersect(rng, txt) Then

line.Select
Selection.Delete xlShiftUp


Else

x = x + 1

End If
Loop
End Sub

This gets an error 438 when trying to set the 'line' variable. I suspect it has something to do with the variable inside the variable but I am not sure how to correct it.

Any help would be greatly appreciated. Thanks in advance.

 

[Peter_SSs]

The issue is that you have earlier set format = Sheets("Formatting")
Since this was not qualified to another workbook it would be in the active workbook, presuming that is also where the code is.
That is, format is already qualified to the active workbook & vba is objecting to you doing it again. So remove ActiveWorkbook from that line.
Further, you will also have trouble with that line if 'Formatting' is not the active sheet when the code is run because ..

Set line = ActiveWorkbook.format.Range(Cells(x, 1), Cells(x, 11))

.. those two red cells, not being qualified to a worksheet will default to the active sheet. That would mean you would be trying to set a range on 'Formatting' using two cells on a different sheet: result -> error
You should use this instead (though note my final paragraph below)

Set line = format.Range(format.Cells(x, 1), format.Cells(x, 11))

You will have similar issues in the next couple of lines as well.

One other piece of advice: Don't use words that vba already has as part of its vocabulary as variable, procedure or module names (eg Format is a function in vba so don't use that as a variable. Perhaps wsFormat instead for that worksheet variable name)

 

[HaHoBe]

Hi shmevan11,

in addition to what Peter_SSs pointed out using Intersect between ranges on two different sheets will return an error due to MS (most probably 1004).

I think using AutoFilter or WorksheetFunction.CountIf should do better than looping through a range.

Ciao,
Holger

 

[Shmevan11]

The issue is that you have earlier set format = Sheets("Formatting")
Since this was not qualified to another workbook it would be in the active workbook, presuming that is also where the code is.
That is, format is already qualified to the active workbook & vba is objecting to you doing it again. So remove ActiveWorkbook from that line.
Further, you will also have trouble with that line if 'Formatting' is not the active sheet when the code is run because ..

Set line = ActiveWorkbook.format.Range(Cells(x, 1), Cells(x, 11))

.. those two red cells, not being qualified to a worksheet will default to the active sheet. That would mean you would be trying to set a range on 'Formatting' using two cells on a different sheet: result -> error
You should use this instead (though note my final paragraph below)

Set line = format.Range(format.Cells(x, 1), format.Cells(x, 11))

You will have similar issues in the next couple of lines as well.

One other piece of advice: Don't use words that vba already has as part of its vocabulary as variable, procedure or module names (eg Format is a function in vba so don't use that as a variable. Perhaps wsFormat instead for that worksheet variable name)

Thanks for the advice!

 

[Shmevan11]

Hi shmevan11,

in addition to what Peter_SSs pointed out using Intersect between ranges on two different sheets will return an error due to MS (most probably 1004).

I think using AutoFilter or WorksheetFunction.CountIf should do better than looping through a range.

Ciao,
Holger

You were right, I received an error 1004. I ended up using CountIf instead. Thanks!

 

[Peter_SSs]

Thanks for the advice!

You're welcome. Hope it is all sorted now.