If Statement with multiple qualifiers

[mworth01]

Hopefully my title is somewhat descriptive of my question. I have one column (B) of text data. I want to write a macro that will go down this column and look at the text one row at a time. Depending on what the cell's content is, it will then assign a number to the adjacent column (C). My macro works fine as long as I only have one qualifier in my If statement. For example, my activecell is the empty column that I want to assign the number to.

This will work:
Do
If ActiveCell.Offset(0,-1) = "BALL" Then Activecell = 3
ElseIf ActiveCell.Offset(0,-1) = "HAMMER" Then Activecell = 8
etc.
EndIf
ActiveCell.Offset(1,0).Activate
Loop Until....

What I want is:
Do
If ActiveCell.Offset(0,-1) = "BALL" Or "MOUSE" Or "STAPLER" Then Activecell = 3
ElseIf ActiveCell.Offset(0,-1) = "HAMMER" Or "PEN" Then Activecell = 8
etc.
EndIf
ActiveCell.Offset(1,0).Activate
Loop Until....

The above gets a mismatch error. How do I arrange my possible options to condense my code so that I don't have to have an elseif statement for every single option? Thanks for any responses.

[hideki]

I often found this kind of error. Try repeat your "ActiveCell.Offset(0,-1) =" to other criteria too like below.

ActiveCell.Offset(0,-1) = "BALL" Or ActiveCell.Offset(0,-1) = "MOUSE" Or ActiveCell.Offset(0,-1) = "STAPLER"

[Jim Thomlinson]

Try this

Do
If ActiveCell.Offset(0,-1) = "BALL" Or ActiveCell.Offset(0,-1) = "MOUSE" Or
ActiveCell.Offset(0,-1) = "STAPLER" Then
Activecell = 3
ElseIf ActiveCell.Offset(0,-1) = "HAMMER" Or ActiveCell.Offset(0,-1) = "PEN"
Then
Activecell = 8
etc.
EndIf
ActiveCell.Offset(1,0).Activate
Loop Until....


--
HTH...

Jim Thomlinson


"mworth01" wrote:

>
> Hopefully my title is somewhat descriptive of my question. I have one
> column (B) of text data. I want to write a macro that will go down
> this column and look at the text one row at a time. Depending on what
> the cell's content is, it will then assign a number to the adjacent
> column (C). My macro works fine as long as I only have one qualifier
> in my If statement. For example, my activecell is the empty column
> that I want to assign the number to.
>
> This will work:
> Do
> If ActiveCell.Offset(0,-1) = "BALL" Then Activecell = 3
> ElseIf ActiveCell.Offset(0,-1) = "HAMMER" Then Activecell =
> 8
> etc.
> EndIf
> ActiveCell.Offset(1,0).Activate
> Loop Until....
>
> What I want is:
> Do
> If ActiveCell.Offset(0,-1) = "BALL" Or "MOUSE" Or "STAPLER" Then
> Activecell = 3
> ElseIf ActiveCell.Offset(0,-1) = "HAMMER" Or "PEN" Then
> Activecell = 8
> etc.
> EndIf
> ActiveCell.Offset(1,0).Activate
> Loop Until....
>
> The above gets a mismatch error. How do I arrange my possible options
> to condense my code so that I don't have to have an elseif statement
> for every single option? Thanks for any responses.
>
>
> --
> mworth01
> ------------------------------------------------------------------------
> mworth01's Profile: http://www.excelforum.com/member.php...fo&userid=8991
> View this thread: http://www.excelforum.com/showthread...hreadid=399472
>
>

[David Hepner]

Try this:

Do
If ActiveCell.Offset(0, -1).Value = "BALL" Or ActiveCell.Offset(0, -1).Value
= "MOUSE" Or ActiveCell.Offset(0, -1).Value = "STAPLER" Then
ActiveCell = 3
ElseIf ActiveCell.Offset(0, -1).value = "HAMMER" Or ActiveCell.Offset(0,
-1).value ="PEN" Then
ActiveCell = 8
End If



"mworth01" wrote:

>
> Hopefully my title is somewhat descriptive of my question. I have one
> column (B) of text data. I want to write a macro that will go down
> this column and look at the text one row at a time. Depending on what
> the cell's content is, it will then assign a number to the adjacent
> column (C). My macro works fine as long as I only have one qualifier
> in my If statement. For example, my activecell is the empty column
> that I want to assign the number to.
>
> This will work:
> Do
> If ActiveCell.Offset(0,-1) = "BALL" Then Activecell = 3
> ElseIf ActiveCell.Offset(0,-1) = "HAMMER" Then Activecell =
> 8
> etc.
> EndIf
> ActiveCell.Offset(1,0).Activate
> Loop Until....
>
> What I want is:
> Do
> If ActiveCell.Offset(0,-1) = "BALL" Or "MOUSE" Or "STAPLER" Then
> Activecell = 3
> ElseIf ActiveCell.Offset(0,-1) = "HAMMER" Or "PEN" Then
> Activecell = 8
> etc.
> EndIf
> ActiveCell.Offset(1,0).Activate
> Loop Until....
>
> The above gets a mismatch error. How do I arrange my possible options
> to condense my code so that I don't have to have an elseif statement
> for every single option? Thanks for any responses.
>
>
> --
> mworth01
> ------------------------------------------------------------------------
> mworth01's Profile: http://www.excelforum.com/member.php...fo&userid=8991
> View this thread: http://www.excelforum.com/showthread...hreadid=399472
>
>