| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select Case
Aaron,
The Loop statement needs to be outside of the Select/End Select statement. Ken "Aaron" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select Case
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 Aaron wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select Case
I think this does -something -like what you want:Public Sub Username2Line() Dim LastRowWS As Long LastRowWS = Cells(Rows.Count, "A").End(xlUp).Row Range("H2").Select 'a guess as to the starting point Do While ActiveCell.Row <= LastRowWS With ActiveCell If (.Value = "MFG16" And .Offset(, 1).Value = "STGFS") _ Or (.Value = "MFG17" And .Offset(, 1).Value = "35FS") _ Or (.Value = "MFG18" And .Offset(, 1).Value = "DPFS") _ Or (.Value = "MFG4" And .Offset(, 1).Value = "70FS") _ Or (.Value = "MFG5" And .Offset(, 1).Value = "VTFS") _ Or (.Value = "MFG7" And .Offset(, 1).Value = "70FS") _ Or (.Value = "MFG1" And .Offset(, 1).Value = "10FS") _ Or (.Value = "MFG11" And .Offset(, 1).Value = "80FS") _ Or (.Value = "MFG15" And (.Offset(, 1).Value = "VTFS") Or (.Offset(, 1).Value = "TSFS") Or (.Offset(, 1).Value = "LUMFS")) Then Offset(0, 2).Value = "Correct" Else Offset(0, 2).Value = "Incorrect Location" End If Offset(1).Activate End With Loop End Sub But the activating/selecting shouldn't really be necessary.. -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=133400 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select Case
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 "Aaron" wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select Case
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 -- HTH... Jim Thomlinson "Aaron" wrote: 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select Case
First thank everyone for their replys. I am having a error of "Complie
Error: Case without Select Case" "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "Aaron" wrote: 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select Case
First thank everyone for their replys. I am having a error of "Complie Error: Case without Select Case" 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 Case "MFG15" 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) On Thu, 10 Sep 2009 10:20:34 -0700, Aaron wrote: First thank everyone for their replys. I am having a error of "Complie Error: Case without Select Case" "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "Aaron" wrote: 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select Case
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 -- HTH... Jim Thomlinson "Aaron" wrote: First thank everyone for their replys. I am having a error of "Complie Error: Case without Select Case" "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "Aaron" wrote: 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select Case
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 -- Rick (MVP - Excel) "Jim Thomlinson" wrote in message ... 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 -- HTH... Jim Thomlinson "Aaron" wrote: First thank everyone for their replys. I am having a error of "Complie Error: Case without Select Case" "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "Aaron" wrote: 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select Case
|
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select Case
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 -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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 -- Rick (MVP - Excel) "Jim Thomlinson" wrote in message ... 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 -- HTH... Jim Thomlinson "Aaron" wrote: First thank everyone for their replys. I am having a error of "Complie Error: Case without Select Case" "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "Aaron" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can I use Select Case to do this? | Excel Programming | |||
| Case without Select Case error problem | Excel Discussion (Misc queries) | |||
| End Select without Select Case, Block If without End If errors | Excel Programming | |||
| Case Select | Excel Programming | |||
Hybrid Mode
