Case Statement with multiple checks
 

Dear All

I am in need of your expert help

I am trying to write a macro that checks two columns in a case statement.
What I want it to do is check that column "A" in not null and check that
column "B" has a particular value.

If Column a is null then exit the macro if not continue with the checking. I
just cannot figure it out and need help

Thanks

Regards

Newman

Case Statement with multiple checks
 

Hi Newman,

I am not really sure what you are trying to do but see if the following
example will help you to work out what you want.

You need to realise that the case only performs the first true evaluation
even if other true evaluations follow. If none are true then it goes to Else
Case but it is not necessary to include Else Case unless you need it.

Note the method I have used to evaluate an empty cell. I find it the most
reliable because Null and Empty and also a cell with "" are all different and
can be a pain deciding which one to use. However, the one I use sees a zero
and will not evaluate that to empty.

If my example does not help then feel free to get back to me and maybe you
can include a small example of what you require.

Sub Test_Case()
Dim rng1 As Range
Dim c As Range
Dim colBvalue As Variant

'Edit following range to suit the your range
Set rng1 = Sheets("Sheet1").Range("A1:A20")

For Each c In rng1

'Test for no data in cells in column A first
If Len(Trim(c)) = 0 Then
MsgBox "Empty cell address is " & _
c.Address & Chr(13) & "Processing terminated"
End 'This terminates the procedure
End If

'Using case to test values in column B
'Set a variable to = the value in column B
colBvalue = Cells(c.Row, c.Column).Offset(0, 1).Value
Select Case colBvalue
Case 10 'Value in col B is 10
MsgBox "Found value " & 10 & _
" Opposite cell " & c.Address
Case 5 'Value in col B is 5
MsgBox "Found value " & 5 & _
" Opposite cell " & c.Address
Case Else 'Value in column B is none of the above
MsgBox "Opposite cell " & c.Address & _
" Did not meet any case condition"
End Select
Next c

End Sub

Regards,

OssieMac

"Newman Emanouel" wrote:

Dear All

I am in need of your expert help

I am trying to write a macro that checks two columns in a case statement.
What I want it to do is check that column "A" in not null and check that
column "B" has a particular value.

If Column a is null then exit the macro if not continue with the checking. I
just cannot figure it out and need help

Thanks

Regards

Newman

Case Statement with multiple checks
 

If ColumnA_Value = 0 Then End (or Exit Sub)
Select Case ColumnB_Value
Case "X": do something
Case "Y": do other code
Case Else:
End Select

Vlado

"Newman Emanouel" wrote:

Dear All

I am in need of your expert help

I am trying to write a macro that checks two columns in a case statement.
What I want it to do is check that column "A" in not null and check that
column "B" has a particular value.

If Column a is null then exit the macro if not continue with the checking. I
just cannot figure it out and need help

Thanks

Regards

Newman

Case Statement with multiple checks
 

Hi OssieMac

I got your code and it sort of works but just to give you a bit more clarity

Column "A" has a list of job numbers and column "D" has a list of codes
which is part of a validation list. So lets say column "D" is to have say
"A", "N", "C"

I need the code to do a couple of things, the first is to check whether a
job number has been allocated therefore column "A" cannot be null then I need
it to check to see if column "D" has the right code (ie "A", "N", "C"), if
it doesnt to fill the cell colour background yellow and if its ok to move
onto the next line

I hope I am a little more clear about my requirements

Your help is really appreciated.

Regards

Newman


"OssieMac" wrote:

Hi Newman,

I am not really sure what you are trying to do but see if the following
example will help you to work out what you want.

You need to realise that the case only performs the first true evaluation
even if other true evaluations follow. If none are true then it goes to Else
Case but it is not necessary to include Else Case unless you need it.

Note the method I have used to evaluate an empty cell. I find it the most
reliable because Null and Empty and also a cell with "" are all different and
can be a pain deciding which one to use. However, the one I use sees a zero
and will not evaluate that to empty.

If my example does not help then feel free to get back to me and maybe you
can include a small example of what you require.

Sub Test_Case()
Dim rng1 As Range
Dim c As Range
Dim colBvalue As Variant

'Edit following range to suit the your range
Set rng1 = Sheets("Sheet1").Range("A1:A20")

For Each c In rng1

'Test for no data in cells in column A first
If Len(Trim(c)) = 0 Then
MsgBox "Empty cell address is " & _
c.Address & Chr(13) & "Processing terminated"
End 'This terminates the procedure
End If

'Using case to test values in column B
'Set a variable to = the value in column B
colBvalue = Cells(c.Row, c.Column).Offset(0, 1).Value
Select Case colBvalue
Case 10 'Value in col B is 10
MsgBox "Found value " & 10 & _
" Opposite cell " & c.Address
Case 5 'Value in col B is 5
MsgBox "Found value " & 5 & _
" Opposite cell " & c.Address
Case Else 'Value in column B is none of the above
MsgBox "Opposite cell " & c.Address & _
" Did not meet any case condition"
End Select
Next c

End Sub

Regards,

OssieMac

"Newman Emanouel" wrote:

Dear All

I am in need of your expert help

I am trying to write a macro that checks two columns in a case statement.
What I want it to do is check that column "A" in not null and check that
column "B" has a particular value.

If Column a is null then exit the macro if not continue with the checking. I
just cannot figure it out and need help

Thanks

Regards

Newman

Case Statement with multiple checks
 

Doesn't sound like Case is needed at all to me, just

For i = 1 To LastRow
If Cells(i,"A").Value < "" Then
If Cells(i,"D").Value < "A" < Cells(i,"D").Value < "N" And _
Cells(I,"D").Value < "C" Then
Cells(I,"D").Interior.Colorindex = 6
End If
End If
Next i


You could use case, but it would be superfluous IMO

For I = 1 To lastrow
Select Case Cells(I, "A").Value
Case Is < "":
Select Case Cells(I, "D").Value
Case "A", "N", "C": 'do nothing
Case Else: Cells(I, "D").Interior.ColorIndex = 6
End Select
End Select
Next I

although you might feel the latter is easier to read


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Newman Emanouel" wrote in message
...
Hi OssieMac

I got your code and it sort of works but just to give you a bit more
clarity

Column "A" has a list of job numbers and column "D" has a list of codes
which is part of a validation list. So lets say column "D" is to have say
"A", "N", "C"

I need the code to do a couple of things, the first is to check whether a
job number has been allocated therefore column "A" cannot be null then I
need
it to check to see if column "D" has the right code (ie "A", "N", "C"),
if
it doesnt to fill the cell colour background yellow and if its ok to move
onto the next line

I hope I am a little more clear about my requirements

Your help is really appreciated.

Regards

Newman

Case Statement with multiple checks
 

Bob

Thanks for the info but I cant get you script to work. Do I have to define
any ofthe variables?



"Bob Phillips" wrote:

Doesn't sound like Case is needed at all to me, just

For i = 1 To LastRow
If Cells(i,"A").Value < "" Then
If Cells(i,"D").Value < "A" < Cells(i,"D").Value < "N" And _
Cells(I,"D").Value < "C" Then
Cells(I,"D").Interior.Colorindex = 6
End If
End If
Next i


You could use case, but it would be superfluous IMO

For I = 1 To lastrow
Select Case Cells(I, "A").Value
Case Is < "":
Select Case Cells(I, "D").Value
Case "A", "N", "C": 'do nothing
Case Else: Cells(I, "D").Interior.ColorIndex = 6
End Select
End Select
Next I

although you might feel the latter is easier to read


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Newman Emanouel" wrote in message
...
Hi OssieMac

I got your code and it sort of works but just to give you a bit more
clarity

Column "A" has a list of job numbers and column "D" has a list of codes
which is part of a validation list. So lets say column "D" is to have say
"A", "N", "C"

I need the code to do a couple of things, the first is to check whether a
job number has been allocated therefore column "A" cannot be null then I
need
it to check to see if column "D" has the right code (ie "A", "N", "C"),
if
it doesnt to fill the cell colour background yellow and if its ok to move
onto the next line

I hope I am a little more clear about my requirements

Your help is really appreciated.

Regards

Newman

Case Statement with multiple checks
 

Sorry, there was a typo in the first loop, it should be

For i = 1 To LastRow
If Cells(i, "A").Value < "" Then
If Cells(i, "D").Value < "A" And Cells(i, "D").Value < "N" And
_
Cells(i, "D").Value < "C" Then
Cells(i, "D").Interior.ColorIndex = 6
End If
End If
Next i


You should declare the variables i and LastRow, and somehow set the LastRow
variable.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Newman Emanouel" wrote in message
...
Bob

Thanks for the info but I cant get you script to work. Do I have to define
any ofthe variables?



"Bob Phillips" wrote:

Doesn't sound like Case is needed at all to me, just

For i = 1 To LastRow
If Cells(i,"A").Value < "" Then
If Cells(i,"D").Value < "A" < Cells(i,"D").Value < "N" And
_
Cells(I,"D").Value < "C" Then
Cells(I,"D").Interior.Colorindex = 6
End If
End If
Next i


You could use case, but it would be superfluous IMO

For I = 1 To lastrow
Select Case Cells(I, "A").Value
Case Is < "":
Select Case Cells(I, "D").Value
Case "A", "N", "C": 'do nothing
Case Else: Cells(I, "D").Interior.ColorIndex = 6
End Select
End Select
Next I

although you might feel the latter is easier to read


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Newman Emanouel" wrote in message
...
Hi OssieMac

I got your code and it sort of works but just to give you a bit more
clarity

Column "A" has a list of job numbers and column "D" has a list of codes
which is part of a validation list. So lets say column "D" is to have
say
"A", "N", "C"

I need the code to do a couple of things, the first is to check whether
a
job number has been allocated therefore column "A" cannot be null then
I
need
it to check to see if column "D" has the right code (ie "A", "N",
"C"),
if
it doesnt to fill the cell colour background yellow and if its ok to
move
onto the next line

I hope I am a little more clear about my requirements

Your help is really appreciated.

Regards

Newman