range or string or long type

B

bartman1980

I've made this code but with this line he gives an error because the
types of rng5 doesn't match.
If Not rng5 Is Nothing Or rng5 Is "a" Then
Does somebody know how I can combine the 2 functions with the OR?

This is my code:
Dim Walternatief As Long
Dim Wbenodigde As Long
Dim rng5 As Range

Range("A:A").Select
Selection.Find(What:="alternatief", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Select
Walternatief = Selection.Row

Dim rng4 As Range
On Error Resume Next
Set rng4 = ActiveSheet.Range("F" & WOptioneel + 1 & ":F" &
Walternatief - 6).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng4 Is Nothing Then
rng4.EntireRow.Delete
End If

Range("A:A").Select
Selection.Find(What:="Benodigde", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Select
Wbenodigde = Selection.Row

On Error Resume Next
Set rng5 = ActiveSheet.Range("F" & Walternatief + 1 & ":F" &
Wbenodigde - 1).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng5 Is Nothing Or rng5 Is "a" Then
rng5.EntireRow.Delete
End If
 
D

Dave Peterson

What happens when rng5 is not nothing, but it's a single cell not equal to "a"?

It seems that if rng5 is something(!), then that would supersede any other
requirement.

if rng5 is nothing then
'do nothing
else
rng5.entirerow.delete
end if

But that's just a guess.
 
G

Guest

Nest the IFs to test rng5 against nothing first and the value of rng5 inside
the first IF.

Don't use "is" to test rng5 against "a", use something like:

If rng5.Value="a" then
rng5.EntireRow.Delete
End If

The above will only work if rng5 is a single cell
 
I

Ian

I'm not sure if you can combine them, but you can use If within If.
Something like:

If Not rng5 Is Nothing Then
If Not rng5 Is "a" Then
rng5.EntireRow.Delete
End If
End If

I've not tested the actual code, but the theory should work. If either
condition is not met, then the delete line will not be executed.

Ian
 
B

bartman1980

A very cursory look suggest
="a"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software










- Tekst uit oorspronkelijk bericht weergeven -
Click to expand...

Hi Don,
I already tried it.
This gives the error: types aren't the same
 
B

bartman1980

Nest the IFs to test rng5 against nothing first and the value of rng5 inside
the first IF.

Don't use "is" to test rng5 against "a", use something like:

If rng5.Value="a" then
rng5.EntireRow.Delete
End If

The above will only work if rng5 is a single cell

--
Gary''s Student - gsnu2007a










- Tekst uit oorspronkelijk bericht weergeven -
Click to expand...

Hi Don, Ian and Gary,
Hi Don,
I already tried your versions.
But all give the same error: types aren't the same

I think it has to do with the type of rng5.
The range is only one cell.
 
B

bartman1980

Hi bartman

If you nest the Ifs, which line does it produce the error on?

Ian








- Tekst uit oorspronkelijk bericht weergeven -
Click to expand...

Hi Ian,

This is my new code :
Dim Walternatief As Long 'this is 70, but could be a different number
depending of how many lines are filled
Dim Wbenodigde As Long 'this is always higher than 84, , but could be
a different number depending of how many lines_
are filled

Dim rng5 As Range
On Error Resume Next
Set rng5 = ActiveSheet.Range("F" & Walternatief + 1 & ":F" &
Wbenodigde - 1).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng5 Is Nothing Then
If Not rng5 = "a" Then ' at this line he gives the ERROR
rng5.EntireRow.Delete
End If
End If
 
I

Ian

Hi bartman

It looks like rng5 is NOT a single cell, but I can't be certain. I don't
know how you'd determine this.

Ian
 
M

michael.beckinsale

Hi Bartman,

Perhaps

If Not rng5.Value = "a" Then

or

If rng5.Value <> "a" Then

Regards

Michael Beckinsale
 
D

Daniel Gash-Jones

Joined
Nov 5, 2007
Messages
18
Reaction score
0
If rng5 is only selecting blank cells then by definition rng5 will be nothing and will not contain any cells with the value "a"
 
D

Dave Peterson

Your code is trying to look for blank cells in that range. Rng5 will be nothing
or that range of blank cells.

How can that range of blank cells ever be equal to "a"--even if it's a single
cell.

And you can't test multiple cells against a single string.

What do you really want to do?
 
B

bartman1980

Your code is trying to look for blank cells in that range. Rng5 will be nothing
or that range of blank cells.

How can that range of blank cells ever be equal to "a"--even if it's a single
cell.

And you can't test multiple cells against a single string.

What do you really want to do?



bartman1980 wrote:
Click to expand...

Hi Dave (and the rest)

I have put the before and after rows here:
Before:
A B
1 Toegangscontrole
2 1 a
3 1 o
4 1
5 CCTV
6 Inbraakdetectie
7 Werktijdregistratie
8 Kaartproductie
9 Service
10
11 Optioneel
12 1 a
13 1 o
14
15
16
17
18
19 Alternatief
20 Toegangscontrole
21 1 a
22 1 o
23 1
24 CCTV
25 Inbraakdetectie
26 Benodigde

After:
A B
1 Toegangscontrole
2 1
3 CCTV
4 Inbraakdetectie
5 Werktijdregistratie
6 Kaartproductie
7 Service
8
9 Optioneel
10 1 o
11
12
13
14
15
16 Alternatief
17 Toegangscontrole
18 1 a
19 CCTV
20 Inbraakdetectie
21 Benodigde

I deleted every line IF A => 1 AND B = "a" OR B = "o" between the
cells "toegangscontrole" and "optioneel"
I deleted every line IF A => 1 AND B = "a" OR B = "" between the cells
"optioneel" and "alternatief"

I deleted every line IF A => 1 AND B = "o" OR B = "" between the cells
"alternatief" and "benodigde"

In this example you can see where the cells "toegangscontrole",
"optioneel", "alternatief" and "benodigde" are.
But they could be in every column, therefor I have to look for the
cells and then use the rownumber.

This is my code but doesn't work the way I want:
sub deletetest()
Dim WOptioneel As Long
Dim Walternatief As Long
Dim Wbenodigde As Long
Dim rng4 As Range
Dim rng5 As Range

Range("A:A").Select
Selection.Find(What:="Optioneel", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Select
WOptioneel = Selection.Row

Range("A:A").Select
Selection.Find(What:="alternatief", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Select
Walternatief = Selection.Row

On Error Resume Next
Set rng4 = ActiveSheet.Range("B" & WOptioneel + 1 & ":B" &
Walternatief - 6).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng4 Is Nothing Then
rng4.EntireRow.Delete
End If

Range("A:A").Select
Selection.Find(What:="Benodigde", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Select
Wbenodigde = Selection.Row

On Error Resume Next
Set rng5 = ActiveSheet.Range("B" & Walternatief + 1 & ":B" &
Wbenodigde - 1).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng5 Is Nothing Then
rng5.EntireRow.Delete
End If
End Sub

I'm not getting desparete, but I'm sure it is going to work sometime.
 
D

Dave Peterson

You could set up an array of 3 topmost strings, 3 bottommost strings, and 3
rules to follow, but I didn't. I just copied and pasted. If I had more than 3
and knew exactly what any future rules could be, I'd set up a loop.

Anyway, this finds the topmost string, the bottommost string, then loops through
those rows.

Then it does it again.

And again.

I didn't test it, but it did compile:

Option Explicit

'I deleted every line IF A => 1 AND B = "a" OR B = "o" between the
'Cells "toegangscontrole" And "optioneel"
'I deleted every line IF A => 1 AND B = "a" OR B = "" between the cells
'"optioneel" and "alternatief"
'
'I deleted every line IF A => 1 AND B = "o" OR B = "" between the cells
'"alternatief" and "benodigde"
Sub testme()

Dim wks As Worksheet
Dim TopRow As Long
Dim BotRow As Long
Dim FoundCell As Range
Dim iRow As Long

Set wks = Worksheets("sheet1")

With wks
'do the first requirement
'find the topmost cell
Set FoundCell = .Cells.Find(what:="toegangscontrole", _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchdirection:=xlNext, _
searchorder:=xlByRows, _
MatchCase:=False)

If FoundCell Is Nothing Then
MsgBox "toegangscontrole wasn't found"
Else
TopRow = FoundCell.Row
'find the bottommost cell
Set FoundCell = .Cells.Find(what:="optioneel", _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows, _
MatchCase:=False)
If FoundCell Is Nothing Then
MsgBox "optioneel wasn't found"
Else
BotRow = FoundCell.Row
For iRow = BotRow To TopRow Step -1
'IF A => 1 AND B = "a" OR B = "o"
If .Cells(iRow, "A").Value > 1 _
And (LCase(.Cells(iRow, "B").Value) = "a" _
Or LCase(.Cells(iRow, "B").Value) = "o") Then
.Rows(iRow).Delete
End If
Next iRow
End If
End If


'do the 2nd requirement
'find the topmost cell
Set FoundCell = .Cells.Find(what:="optioneel", _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchdirection:=xlNext, _
searchorder:=xlByRows, _
MatchCase:=False)

If FoundCell Is Nothing Then
MsgBox "optioneel wasn't found"
Else
TopRow = FoundCell.Row
'find the bottommost cell
Set FoundCell = .Cells.Find(what:="alternatief", _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows, _
MatchCase:=False)
If FoundCell Is Nothing Then
MsgBox "alternatief wasn't found"
Else
BotRow = FoundCell.Row
For iRow = BotRow To TopRow Step -1
'IF A => 1 AND B = "a" OR B = ""
If .Cells(iRow, "A").Value > 1 _
And (LCase(.Cells(iRow, "B").Value) = "a" _
Or LCase(.Cells(iRow, "B").Value) = "") Then
.Rows(iRow).Delete
End If
Next iRow
End If
End If


'do the 3rd requirement
'find the topmost cell
Set FoundCell = .Cells.Find(what:="alternatief", _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchdirection:=xlNext, _
searchorder:=xlByRows, _
MatchCase:=False)

If FoundCell Is Nothing Then
MsgBox "alternatief wasn't found"
Else
TopRow = FoundCell.Row
'find the bottommost cell
Set FoundCell = .Cells.Find(what:="benodigde", _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows, _
MatchCase:=False)
If FoundCell Is Nothing Then
MsgBox "benodigde wasn't found"
Else
BotRow = FoundCell.Row
For iRow = BotRow To TopRow Step -1
'IF A => 1 AND B = "o" OR B = ""
If .Cells(iRow, "A").Value > 1 _
And (LCase(.Cells(iRow, "B").Value) = "o" _
Or LCase(.Cells(iRow, "B").Value) = "") Then
.Rows(iRow).Delete
End If
Next iRow
End If
End If

End With

End Sub

=====
Notice in the .cells.find portions, I start at the bottom cell
(.cells(.cells.count)) and look for the xlnext occurrence. That's the topmost
occurence.

When I started in the first cell (.cells(1)) and look for the xlprevious
occurrence, that's the bottommost occurence.
 
B

bartman1980

You could set up an array of 3 topmost strings, 3 bottommost strings, and 3
rules to follow, but I didn't. I just copied and pasted. If I had more than 3
and knew exactly what any future rules could be, I'd set up a loop.

Anyway, this finds the topmost string, the bottommost string, then loops through
those rows.

Then it does it again.

And again.

I didn't test it, but it did compile:

Option Explicit

'I deleted every line IF A => 1 AND B = "a" OR B = "o" between the
'Cells "toegangscontrole" And "optioneel"
'I deleted every line IF A => 1 AND B = "a" OR B = "" between the cells
'"optioneel" and "alternatief"
'
'I deleted every line IF A => 1 AND B = "o" OR B = "" between the cells
'"alternatief" and "benodigde"
Sub testme()

Dim wks As Worksheet
Dim TopRow As Long
Dim BotRow As Long
Dim FoundCell As Range
Dim iRow As Long

Set wks = Worksheets("sheet1")

With wks
'do the first requirement
'find the topmost cell
Set FoundCell = .Cells.Find(what:="toegangscontrole", _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchdirection:=xlNext, _
searchorder:=xlByRows, _
MatchCase:=False)

If FoundCell Is Nothing Then
MsgBox "toegangscontrole wasn't found"
Else
TopRow = FoundCell.Row
'find the bottommost cell
Set FoundCell = .Cells.Find(what:="optioneel", _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows, _
MatchCase:=False)
If FoundCell Is Nothing Then
MsgBox "optioneel wasn't found"
Else
BotRow = FoundCell.Row
For iRow = BotRow To TopRow Step -1
'IF A => 1 AND B = "a" OR B = "o"
If .Cells(iRow, "A").Value > 1 _
And (LCase(.Cells(iRow, "B").Value) = "a" _
Or LCase(.Cells(iRow, "B").Value) = "o") Then
.Rows(iRow).Delete
End If
Next iRow
End If
End If

'do the 2nd requirement
'find the topmost cell
Set FoundCell = .Cells.Find(what:="optioneel", _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchdirection:=xlNext, _
searchorder:=xlByRows, _
MatchCase:=False)

If FoundCell Is Nothing Then
MsgBox "optioneel wasn't found"
Else
TopRow = FoundCell.Row
'find the bottommost cell
Set FoundCell = .Cells.Find(what:="alternatief", _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows, _
MatchCase:=False)
If FoundCell Is Nothing Then
MsgBox "alternatief wasn't found"
Else
BotRow = FoundCell.Row
For iRow = BotRow To TopRow Step -1
'IF A => 1 AND B = "a" OR B = ""
If .Cells(iRow, "A").Value > 1 _
And (LCase(.Cells(iRow, "B").Value) = "a" _
Or LCase(.Cells(iRow, "B").Value) = "") Then
.Rows(iRow).Delete
End If
Next iRow
End If
End If

'do the 3rd requirement
'find the topmost cell
Set FoundCell = .Cells.Find(what:="alternatief", _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchdirection:=xlNext, _
searchorder:=xlByRows, _
MatchCase:=False)

If FoundCell Is Nothing Then
MsgBox "alternatief wasn't found"
Else
TopRow = FoundCell.Row
'find the bottommost cell
Set FoundCell = .Cells.Find(what:="benodigde", _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows, _
MatchCase:=False)
If FoundCell Is Nothing Then
MsgBox "benodigde wasn't found"
Else
BotRow = FoundCell.Row
For iRow = BotRow To TopRow Step -1
'IF A => 1 AND B = "o" OR B = ""
If .Cells(iRow, "A").Value > 1 _
And (LCase(.Cells(iRow, "B").Value) = "o" _
Or LCase(.Cells(iRow, "B").Value) = "") Then
.Rows(iRow).Delete
End If
Next iRow
End If
End If

End With

End Sub

=====
Notice in the .cells.find portions, I start at the bottom cell
(.cells(.cells.count)) and look for the xlnext occurrence. That's the topmost
occurence.

When I started in the first cell (.cells(1)) and look for the xlprevious
occurrence, that's the bottommost occurence.



















--

Dave Peterson- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -
Click to expand...

Hi Dave,
It doesn't work.
He gives twices the error Otioneel wasnt found and then the error
benodigde wasnt found.
 
D

Dave Peterson

The .finds are looking for a match for the whole cell (lookat:=xlWhole). If
those strings are in the cell with other stuff, then change it to
lookat:=xlPart,

ps. You may have seen that most of the responders in these newsgroups are top
posters. You may want to start top posting, too. Or snip the stuff that isn't
necessary.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

conso macro 0
type mismatch on find function 7
Need help with correct syntax please 2
If Cells.Find returns run-time error '91': Object variableor With block variable not set 1
Resize to last entry in row and copy to sheet 1 3
Data Cleanse Macro Running Very Slow 0
Second Find after a first find fails 3
Any way to pretty this up? 6

Top