Macro - with If statements

[Nyanko]

Hi,

The below code is part of a larger routine, but I'm having trouble getting the order correct !

What I want to happen depends on the value of "Flag" For some values it's simply creating a value to be used later. But for some the process needed is more complex.

  Do While Flag <> "" 'look down rows if cells do not equal ""
    If Flag = "" Then Exit For 
    If Flag = "LTS" Then OtherFlag = "Long Term Sick"
    If Flag = "SICKR" Then OtherFlag = "Sick on Rest Day"
                  rowcount = rowcount + 1
                  Flag = Cells(rowcount, colcount)
    If Flag = "x" Then rowcount = rowcount + 1
                 Flag = Cells(rowcount, colcount)
  Loop

As you can see (hopefully !) if Flag equals "x" or "SICKR" there are several operations that need to happen for that instance only. However what is happening is that the code is being read as linear whereas I need it to skip to each If statement.

I really really hope that makes sense !!!

 

[VoG]

Try

Do While Flag <> "" 'look down rows if cells do not equal ""
    If Flag = "" Then
        Exit For
    ElseIf Flag = "LTS" Then
        OtherFlag = "Long Term Sick"
    ElseIf Flag = "SICKR" Then
        OtherFlag = "Sick on Rest Day"
        RowCount = RowCount + 1
        Flag = Cells(RowCount, colcount)
    ElseIf Flag = "x" Then
        RowCount = RowCount + 1
        Flag = Cells(RowCount, colcount)
    End If
  Loop

 

[Norie]

What For are you exiting here?

If Flag = "" Then Exit For

 

[Nyanko]

What For are you exiting here?

If Flag = "" Then Exit For

It's part of a larger code that works fine ... it's just to show here that it's where the code needs to exit.

VoG II - that looks like a good solution, I'll give it a try.

Many thanks

 

[Nyanko]

Try

Do While Flag <> "" 'look down rows if cells do not equal ""
    If Flag = "" Then
        Exit For
    ElseIf Flag = "LTS" Then
        OtherFlag = "Long Term Sick"
    ElseIf Flag = "SICKR" Then
        OtherFlag = "Sick on Rest Day"
        RowCount = RowCount + 1
        Flag = Cells(RowCount, colcount)
    ElseIf Flag = "x" Then
        RowCount = RowCount + 1
        Flag = Cells(RowCount, colcount)
    End If
  Loop

Hi, I try this and get the error :
Compile Error - Else without If

 

[hsk]

whyy don't to try "Select case" .... that will make code more clean and easy to work with something like

Select Case Flag
  Case ""
      Exit ..... what ever actioin u want 
  Case "LTS"
     OtherFlag = "Long Term Sick"
  Case "SICKR"
     OtherFlag = "Sick on Rest Day"
     RowCount = RowCount + 1
     Flag = Cells(RowCount, colcount)
  Case "x"
     RowCount = RowCount + 1
     Flag = Cells(RowCount, colcount)
  Case Else
      MsgBox "Invalid Flag" 
      Exit ..... what ever actioin u want
End Select