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

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

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

I often found this kind of error. Try repeat you
"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

--
hidek
-----------------------------------------------------------------------
hideki's Profile: http://www.excelforum.com/member.php...fo&userid=1890
View this thread: http://www.excelforum.com/showthread.php?threadid=39947