Macro - with If statements

Nyanko

Active Member
Joined
Sep 1, 2005
Messages
437
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.

Code:
  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 !!!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
What For are you exiting here?
Code:
If Flag = "" Then Exit For
 

Nyanko

Active Member
Joined
Sep 1, 2005
Messages
437
What For are you exiting here?
Code:
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

Active Member
Joined
Sep 1, 2005
Messages
437
Try

Code:
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

Well-known Member
Joined
Oct 19, 2006
Messages
567
whyy don't to try "Select case" .... that will make code more clean and easy to work with something like

Code:
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
 

Forum statistics

Threads
1,181,647
Messages
5,931,208
Members
436,784
Latest member
amuljono

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top