Select Case

A

Aaron

Hello All,

I have the following Code giving me some problems. Can anyone help? I have
little knowledge of VBA. Thanks in advance.

Public Sub Username2Line()



Dim LastRowWS As Long
LastRowWS = Cells(Rows.Count, "A").End(xlUp).Row


Do Until ActiveCell = ("H" & (LastRowWS))
Select Case ActiveCell.Value

Case "MFG1"

If ActiveCell.Offset(0, 1).Value = "10FS" Then
ActiveCell.Offset(0, 2).Value = "Correct"
Else
ActiveCell.Offset(0, 2).Value = "Incorrect Location"
End If

Case "MFG11"

If ActiveCell.Offset(0, 1).Value = "80FS" Then
ActiveCell.Offset(0, 2).Value = "Correct"
Else
ActiveCell.Offset(0, 2).Value = "Incorrect Location"
End If

Case "MFG15"

If ActiveCell.Offset(0, 1).Value = "VTFS" Then
ActiveCell.Offset(0, 2).Value = "Correct"
Else
If ActiveCell.Offset(0, 1).Value = "TSFS" Then
ActiveCell.Offset(0, 2).Value = "Correct"
Else
If ActiveCell.Offset(0, 1).Value = "LUMFS" Then
ActiveCell.Offset(0, 2).Value = "Correct"
Else
ActiveCell.Offset(0, 2).Value = "Incorrect Location"
End If

Case "MFG16"

If ActiveCell.Offset(0, 1).Value = "STGFS" Then
ActiveCell.Offset(0, 2).Value = "Correct"
Else
ActiveCell.Offset(0, 2).Value = "Incorrect Location"
End If

Case "MFG17"

If ActiveCell.Offset(0, 1).Value = "35FS" Then
ActiveCell.Offset(0, 2).Value = "Correct"
Else
ActiveCell.Offset(0, 2).Value = "Incorrect Location"
End If

Case "MFG18"

If ActiveCell.Offset(0, 1).Value = "DPFS" Then
ActiveCell.Offset(0, 2).Value = "Correct"
Else
ActiveCell.Offset(0, 2).Value = "Incorrect Location"
End If

Case "MFG4"

If ActiveCell.Offset(0, 1).Value = "70FS" Then
ActiveCell.Offset(0, 2).Value = "Correct"
Else
ActiveCell.Offset(0, 2).Value = "Incorrect Location"
End If

Case "MFG5"

If ActiveCell.Offset(0, 1).Value = "VTFS" Then
ActiveCell.Offset(0, 2).Value = "Correct"
Else
ActiveCell.Offset(0, 2).Value = "Incorrect Location"
End If

Case "MFG7"

If ActiveCell.Offset(0, 1).Value = "70FS" Then
ActiveCell.Offset(0, 2).Value = "Correct"
Else
ActiveCell.Offset(0, 2).Value = "Incorrect Location"
End If

Loop


End Select


End Sub
 
K

Ken Warthen

Aaron,

The Loop statement needs to be outside of the Select/End Select statement.

Ken
 
D

Dana DeLouis

Hi. At first glance, it looks like you should "End Select", then loop.
Can't tell where your active cell starts.
The Active cell appears not to move.
Column A, and Column H appear not to be related.


In general...

Set your active cell.

Do Until ActiveCell = ("H" & (LastRowWS))
Select Case xxx
'..etc
End Select

Move Active Cell 'Make sure you move down 'H
Loop

' = = = = =
HTH :>)
Dana DeLouis
 
M

Matthew Herbert

Aaron,

What "problems" are you having? My general suspicion is that you need to
fix the Do Loop. Take a look at the help documentation in the Visual Basic
Editor and search "Do Loop". Read the documentation that comes up related to
do loops.

I haven't looked too closely at your code, but there doesn't appear to be
any "counter" that increases the row number for your "Do Until ActiveCell =
("H" & (LastRowWS)" line of code. Additionally, the statement evaluates
ActiveCell against H#. H# is a string address which can be compared to
ActiveCell.Address(False,False).

For example, if LastRowWS = 10 and the Case = "MFG1" then there is nothing
that advances the "H" & LastRowWS condition of the Do Until statement.

So, assuming the following: ActiveCell is H1 and LastRowWS = 10, then the
first time through the loop will evaluate as listed below.

1. Do Until ActiveCell.Address(False,False) = "H" & LastRowWS
Do Until H1 = H10
2. Select Case ActiveCell.Value
Select Case "MFG1"
3. Evaluate the IF statement to insert "Correct" or "Incorrect Location"
based on the condition
4. Exit the Case statement
5. Reevaluate the Do Until statement (see second loop below)

The second time through the loop looks like the following (note that the
ActiveCell has not advanced, i.e. it is still H1):
1. Do Until ActiveCell.Address(False,False) = "H" & LastRowWS
Do Until H1 = H10
2. Select Case ActiveCell.Value (ActiveCell is H1)
Select Case "MFG1"
3. Evaluate the IF statement to insert "Correct" or "Incorrect Location"
based on the condition
4. Exit the Case statement
5. Reevaluate the Do Until statement

And so on forever (or until Excel tells you there is a resource error). (By
the way, you can force a break in the code with Ctrl + Pause/Break).

Thus, the loop will indefinitely evaluate H1 = H10 because there is nothing
within the Do Loop to advance the ActiveCell. So, within the Case "MFG1",
you could insert something like ActiveCell.Offset(1, 0).Select AFTER the End
If. You'll need to repeat this for each of the Case statements. So, try
adding the .Address(False, False) and the above mentioned Offset within the
Case statements.

Since VBA is new to you, start with trying to make this change. There is a
lot of learning that occurs through doing. Once you are comfortable with the
change, we can then take out the ActiveCell.Offset(1, 0).Select and create a
counter instead. (Avoiding .Select or .Activate speeds up process time
considerably). Additionally, we can add a way to validate that the starting
cell (what I've assumed to be H1) is indeed the right cell.

Best,

Matthew Herbert
 
J

Jim Thomlinson

There are a few issues here... As mentioned your loop should not be within
the select. Additional to that your loop is infintie for 2 reasons. One you
never move the active cell. Secondly your test is not checking to see if you
have gone past the last row. it is chacking the value of the active cell...
Try something more like this...

Public Sub Username2Line()

Dim rng As Range
Dim rngAll As Range


Set rngAll = Range(Range("H2"), Cells(Rows.Count, "A").End(xlUp).Offset(0, 7))

For Each rng In rngAll
Select Case rng.Value

Case "MFG1"

If rng.Offset(0, 1).Value = "10FS" Then
rng.Offset(0, 2).Value = "Correct"
Else
rng.Offset(0, 2).Value = "Incorrect Location"
End If

Case "MFG11"

If rng.Offset(0, 1).Value = "80FS" Then
rng.Offset(0, 2).Value = "Correct"
Else
rng.Offset(0, 2).Value = "Incorrect Location"
End If

Case "MFG15"

If rng.Offset(0, 1).Value = "VTFS" Then
rng.Offset(0, 2).Value = "Correct"
Else
If rng.Offset(0, 1).Value = "TSFS" Then
rng.Offset(0, 2).Value = "Correct"
Else
If rng.Offset(0, 1).Value = "LUMFS" Then
rng.Offset(0, 2).Value = "Correct"
Else
rng.Offset(0, 2).Value = "Incorrect Location"
End If

Case "MFG16"

If rng.Offset(0, 1).Value = "STGFS" Then
rng.Offset(0, 2).Value = "Correct"
Else
rng.Offset(0, 2).Value = "Incorrect Location"
End If

Case "MFG17"

If rng.Offset(0, 1).Value = "35FS" Then
rng.Offset(0, 2).Value = "Correct"
Else
rng.Offset(0, 2).Value = "Incorrect Location"
End If

Case "MFG18"

If rng.Offset(0, 1).Value = "DPFS" Then
rng.Offset(0, 2).Value = "Correct"
Else
rng.Offset(0, 2).Value = "Incorrect Location"
End If

Case "MFG4"

If rng.Offset(0, 1).Value = "70FS" Then
rng.Offset(0, 2).Value = "Correct"
Else
rng.Offset(0, 2).Value = "Incorrect Location"
End If

Case "MFG5"

If rng.Offset(0, 1).Value = "VTFS" Then
rng.Offset(0, 2).Value = "Correct"
Else
rng.Offset(0, 2).Value = "Incorrect Location"
End If

Case "MFG7"

If rng.Offset(0, 1).Value = "70FS" Then
rng.Offset(0, 2).Value = "Correct"
Else
rng.Offset(0, 2).Value = "Incorrect Location"
End If

End Select

Next rng

End Sub
 
A

Aaron

First thank everyone for their replys. I am having a error of "Complie
Error: Case without Select Case"
 
C

Chip Pearson

First thank everyone for their replys. I am having a error of "Complie
Error: Case without Select Case"
Click to expand...

That error means that some statement does not have a corresponding End
statement. Just because the message says "Case" does not mean that it
is necessarily the "Case" that is missing its End statement. Check all
your If, Select, With statements to ensure that each has a
corresponding End If, End Select, End With, etc.

A quick review of your code leads me to the If statements in the

block. Ensure that you have all the proper Else, ElseIf, and End If
functions. If you get in the habit of properly indenting your code,
such error become very simple to spot.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
J

Jim Thomlinson

Sorry there was a problem with your else if's try this...

Public Sub Username2Line()

Dim rng As Range
Dim rngAll As Range


Set rngAll = Range(Range("H2"), Cells(Rows.Count, "A").End(xlUp).Offset(0, 7))

For Each rng In rngAll
Select Case rng.Value

Case "MFG1"

If rng.Offset(0, 1).Value = "10FS" Then
rng.Offset(0, 2).Value = "Correct"
Else
rng.Offset(0, 2).Value = "Incorrect Location"
End If

Case "MFG11"

If rng.Offset(0, 1).Value = "80FS" Then
rng.Offset(0, 2).Value = "Correct"
Else
rng.Offset(0, 2).Value = "Incorrect Location"
End If

Case "MFG15"

If rng.Offset(0, 1).Value = "VTFS" Then
rng.Offset(0, 2).Value = "Correct"
ElseIf rng.Offset(0, 1).Value = "TSFS" Then
rng.Offset(0, 2).Value = "Correct"
ElseIf rng.Offset(0, 1).Value = "LUMFS" Then
rng.Offset(0, 2).Value = "Correct"
Else
rng.Offset(0, 2).Value = "Incorrect Location"
End If

Case "MFG16"

If rng.Offset(0, 1).Value = "STGFS" Then
rng.Offset(0, 2).Value = "Correct"
Else
rng.Offset(0, 2).Value = "Incorrect Location"
End If

Case "MFG17"

If rng.Offset(0, 1).Value = "35FS" Then
rng.Offset(0, 2).Value = "Correct"
Else
rng.Offset(0, 2).Value = "Incorrect Location"
End If

Case "MFG18"

If rng.Offset(0, 1).Value = "DPFS" Then
rng.Offset(0, 2).Value = "Correct"
Else
rng.Offset(0, 2).Value = "Incorrect Location"
End If

Case "MFG4"

If rng.Offset(0, 1).Value = "70FS" Then
rng.Offset(0, 2).Value = "Correct"
Else
rng.Offset(0, 2).Value = "Incorrect Location"
End If

Case "MFG5"

If rng.Offset(0, 1).Value = "VTFS" Then
rng.Offset(0, 2).Value = "Correct"
Else
rng.Offset(0, 2).Value = "Incorrect Location"
End If

Case "MFG7"

If rng.Offset(0, 1).Value = "70FS" Then
rng.Offset(0, 2).Value = "Correct"
Else
rng.Offset(0, 2).Value = "Incorrect Location"
End If

End Select

Next rng

End Sub
 
R

Rick Rothstein

I would probably simplify your code like (this in order to remove all the
repeated code lines)...

Public Sub Username2Line()
Dim rng As Range
Dim IsCorrect As Boolean
For Each rng In Range(Range("H2"), Cells(Rows.Count, "A"). _
End(xlUp).Offset(0, 7))
Select Case rng.Value
Case "MFG1"
IsCorrect = rng.Offset(0, 1).Value = "10FS"
Case "MFG11"
IsCorrect = rng.Offset(0, 1).Value = "80FS"
Case "MFG15"
IsCorrect = rng.Offset(0, 1).Value = "VTFS" Or _
rng.Offset(0, 1).Value = "TSFS" Or _
rng.Offset(0, 1).Value = "LUMFS"
Case "MFG16"
IsCorrect = rng.Offset(0, 1).Value = "STGFS"
Case "MFG17"
IsCorrect = rng.Offset(0, 1).Value = "35FS"
Case "MFG18"
IsCorrect = rng.Offset(0, 1).Value = "DPFS"
Case "MFG4"
IsCorrect = rng.Offset(0, 1).Value = "70FS"
Case "MFG5"
IsCorrect = rng.Offset(0, 1).Value = "VTFS"
Case "MFG7"
IsCorrect = rng.Offset(0, 1).Value = "70FS"
End Select
Next
If IsCorrect Then
rng.Offset(0, 2).Value = "Correct"
Else
rng.Offset(0, 2).Value = "Incorrect Location"
End If
End Sub
 
R

Rick Rothstein

Code Correction.
======================

My previously posted code does not work; this modification to it does...

Public Sub Username2Line()
Dim rng As Range
Dim IsCorrect As Boolean
For Each rng In Range(Range("H2"), Cells(Rows.Count, "A"). _
End(xlUp).Offset(0, 7))
If Len(rng.Value) Then
Select Case rng.Value
Case "MFG1"
IsCorrect = rng.Offset(0, 1).Value = "10FS"
Case "MFG11"
IsCorrect = rng.Offset(0, 1).Value = "80FS"
Case "MFG15"
IsCorrect = rng.Offset(0, 1).Value = "VTFS" Or _
rng.Offset(0, 1).Value = "TSFS" Or _
rng.Offset(0, 1).Value = "LUMFS"
Case "MFG16"
IsCorrect = rng.Offset(0, 1).Value = "STGFS"
Case "MFG17"
IsCorrect = rng.Offset(0, 1).Value = "35FS"
Case "MFG18"
IsCorrect = rng.Offset(0, 1).Value = "DPFS"
Case "MFG4"
IsCorrect = rng.Offset(0, 1).Value = "70FS"
Case "MFG5"
IsCorrect = rng.Offset(0, 1).Value = "VTFS"
Case "MFG7"
IsCorrect = rng.Offset(0, 1).Value = "70FS"
End Select
If IsCorrect Then
rng.Offset(0, 2).Value = "Correct"
Else
rng.Offset(0, 2).Value = "Incorrect Location"
End If
End If
Next
End Sub
 
R

Rick Rothstein

That's what I get for writing code directly in a response without testing
it. Not only was the Next out of place, but I had also left out a test for
blank cells. I just posted corrected (still simplified) code against my
previous posting. Thanks for catching my error... much appreciated.
 
D

Dana DeLouis

Hi. Perhaps combine the two 70FS's.

Case "MFG4", "MFG7"
IsCorrect = rng.Offset(0, 1).Value = "70FS"

Not sure how the data is set up, but it appears that the Op will have to
make sure that at least one of the Cases is Run. (Perhaps add an Case Else)
The reason is that if IsCorrect is set to True, and the next pass does
not meet any Case Statements, then IsCorrect still remains set to True.
Maybe...

If IsCorrect Then
rng.Offset(0, 2).Value = "Correct"
IsCorrect = False
Else
...etc

= = = = =
Just an idea.
Dana DeLouis
 
R

Rick Rothstein

Your points are well taken. Thanks. Here is the amended code incorporating
your suggestions...

Public Sub Username2Line()
Dim rng As Range
Dim IsCorrect As Boolean
For Each rng In Range(Range("H2"), Cells(Rows.Count, "A"). _
End(xlUp).Offset(0, 7))
If Len(rng.Value) Then
Select Case rng.Value
Case "MFG1"
IsCorrect = rng.Offset(0, 1).Value = "10FS"
Case "MFG11"
IsCorrect = rng.Offset(0, 1).Value = "80FS"
Case "MFG15"
IsCorrect = rng.Offset(0, 1).Value = "VTFS" Or _
rng.Offset(0, 1).Value = "TSFS" Or _
rng.Offset(0, 1).Value = "LUMFS"
Case "MFG16"
IsCorrect = rng.Offset(0, 1).Value = "STGFS"
Case "MFG17"
IsCorrect = rng.Offset(0, 1).Value = "35FS"
Case "MFG18"
IsCorrect = rng.Offset(0, 1).Value = "DPFS"
Case "MFG4", "MFG7"
IsCorrect = rng.Offset(0, 1).Value = "70FS"
Case "MFG5"
IsCorrect = rng.Offset(0, 1).Value = "VTFS"
Case Else
IsCorrect = False
End Select
If IsCorrect Then
rng.Offset(0, 2).Value = "Correct"
Else
rng.Offset(0, 2).Value = "Incorrect Location"
IsCorrect = False
End If
End If
Next
End Sub
 

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

Runtime error 91: Object variable or With block variable not set 8
My macro doesn't work if sheet is protected but it works if not protected 2
Can Vba workout if a selection is part of a group? 0
Variable Defined as Double is entered in cell with only 2 decimal places 2
Combobox will not show list 4
fill range 4
Inserting 2 columns when cells don't match 6
Need help-For loop 3

Top