Loop without a Do? 1

[willyboy58]

The brief worksheet looks like:

A B C D E F
1 name job 05-01-03 5-02-03
2 Bob 186 6.0 6.0
3 Steve 188 6.0
4 Mike 189 6.0

Row 1 is my headings: 2 on down is the employee name, job # and hours. Columns D and F are the empty cells to place an L and H into.

In the procedure below, if the value in the activecell in column B is equal to 186 or 189 then I want an “L” in the empty column next to the hours in the row for that day (Bob column D; Mike column F). Each row may have more than one entry of hours. If it is 188, then I want an “H” (Steve column D). But I keep getting the error of “Loop without a Do”. Why? I do have a Do. Any help will be greatly appreciated. TIA. Bill

'this sub is to insert the H and P into the column next to the
' TOTAL hours

Sub InsertH_And_PTotalRowCells()
Dim LeaveHours As TextEffectFormat
Dim HolidayHours As TextEffectFormat
LeaveHours = "L"
HolidayHours = "H"

Range("A2&quot.Select
Do
With ActiveCell
If ActiveCell = "" Then
ActiveCell = ActiveCell.Offset(0, 1)
Else
If ActiveCell <> &quot;&quot; Then
ActiveCell.Offset(1, 1).Select
If ActiveCell = 186 Or 189 Then
While Not IsEmpty(ActiveCell)
With ActiveCell()
ActiveCell.Offset(0, 1).Select
ActiveCell = LeaveHours
ActiveCell.Offset(0, 1).Select
End With
Wend
Else
If ActiveCell = 188 Then
While Not IsEmpty(ActiveCell)
With ActiveCell()
ActiveCell.Offset(0, 1).Select
ActiveCell = HolidayHours
ActiveCell.Offset(0, 1).Select
End With
Wend
End If
End If
End If
End If
Loop Until ActiveCell.Formula = 'NOTE: I know I need something here. I have used different cell values but the message is still the same.

End Sub

 

[TonyJollans]

Hi willyboy58,

You are missing an End With to match the With immediately after the Do. I note that you also have two other With blocks inside the code, but never use them so you might as well delete them all.

When I tried to run the code, the WordArt objects gave me a problem but that may be because I don't have your full set up.

Enjoy,
Tony

 

[willyboy58]

Tony,

After adding an End With after the second WEND and before the first End If, I then get the message &quot;End With without a With&quot;. I have three With's and three End With's. I have four If's and four End If's. Unless I just can't see what's missing.

The two With's inside the code are really the heart of the procedure. That's what I am trying to use to put an H or P in the empty column.

TIA.

 

[Zathras]

What Tony has said is that you are not actually using any of the With / End With statements -- they might as well not be there.

For example:

  With ActiveCell()
    ActiveCell.Offset(0, 1).Select
    ActiveCell = HolidayHours
    ActiveCell.Offset(0, 1).Select
  End With

is exactly equivalent to

  ActiveCell.Offset(0, 1).Select
  ActiveCell = HolidayHours
  ActiveCell.Offset(0, 1).Select

Now, if you like, you can make use of With / End With to simplify the code a bit like this:

  With ActiveCell()
    .Offset(0, 1).Select
    .Value = HolidayHours
    .Offset(0, 1).Select
  End With

Do you see the difference? (the leading period indicates to the compiler that you actually want to use the thing you are &quot;withing&quot

 

[willyboy58]

Zathras,

Thanks for the input. My coding techniques are not the best, but I see what you mean. I am getting closer to my goal. I am trying to read the cells in the rows. If the job is 186 or 189 then put an &quot;L&quot; in the cell to the right of the hours and if the job is 188, then place an &quot;H&quot; in the cell to the right. Then move down to the next row for the next employee.

After making changes to the code, I get the message &quot;Loop without Do&quot;. I don't understand why. I have the following code:

Sub InsertH_And_PTotalRowCells2()
Dim ColOffset As Integer
Dim LeaveHours As String
Dim HolidayHours As String
LeaveHours = &quot;L&quot;
HolidayHours = &quot;H&quot;
Range(&quot;A2&quot.Select
Do
If ActiveCell = &quot;&quot; Then
ActiveCell.Offset(0, 1).Select
MsgBox &quot;s/b at B2&quot;
Else
ActiveCell.Offset(1, 1).Select
MsgBox &quot;s/b at B3&quot;

If ActiveCell = 186 Or 189 Then
With ActiveCell.Offset(0, 1)
While IsEmpty(ActiveCell)

For ColOffset = 1 To 26
If .Offset(0, ColOffset) > 0 Then
ActiveCell.Offset(0, 1).Select
ActiveCell = LeaveHours
ActiveCell.Offset(-1, 0).Select
End If
Next ColOffset
Wend

If ActiveCell = 186 Or 189 Then
With ActiveCell.Offset(0, 1)
While IsEmpty(ActiveCell)

For ColOffset = 1 To 26
If .Offset(0, ColOffset) > 0 Then
ActiveCell.Offset(0, 1).Select
ActiveCell = HolidayHours
ActiveCell.Offset(-1, 0).Select
End If
Next ColOffset
Wend

Loop Until ActiveCell = &quot;&quot; And ActiveCell.Offset(0, 1) = &quot;&quot; And ActiveCell.Offset(1, 0) = &quot;&quot; And ActiveCell.Offset(1, 1) = &quot;&quot;
'the last offset above seems to be part of the problem

End Sub

 

[Zathras]

I can't quite figure out what you want to do. Perhaps you could post some test data.

Meanwhile, here is your code with some comments added. Note that if you use a consistent indentation scheme it becomes a little easier to see what's missing. Obviously putting in all of the missing &quot;End&quot; statements at the bottom will not get you what you want, but it does satisfy the compiler and matches the &quot;Do&quot; with the &quot;Loop Until&quot;:
[blue]

Sub InsertH_And_PTotalRowCells2()
Dim ColOffset As Integer
Dim LeaveHours As String
Dim HolidayHours As String
LeaveHours = "L"
HolidayHours = "H"
Range("A2").Select
Do
 If ActiveCell = "" Then

[green]

'<--- No End If

[/color]

   ActiveCell.Offset(0, 1).Select
   MsgBox "s/b at B2"
 Else
   ActiveCell.Offset(1, 1).Select
   MsgBox "s/b at B3"
   
   If ActiveCell = 186 Or 189 Then

[green]

'<--- No End If

[/color]

     With ActiveCell.Offset(0, 1)

[green]

'<--- No End With

[/color]

       While IsEmpty(ActiveCell)
    
         For ColOffset = 1 To 26
           If .Offset(0, ColOffset) > 0 Then
             ActiveCell.Offset(0, 1).Select
             ActiveCell = LeaveHours
             ActiveCell.Offset(-1, 0).Select
           End If
         Next ColOffset
       Wend
                   
       If ActiveCell = 186 Or 189 Then

[green]

'<--- no End If

[/color]

         With ActiveCell.Offset(0, 1)

[green]

'<--- no End With

[/color]

           While IsEmpty(ActiveCell)
           
             For ColOffset = 1 To 26
               If .Offset(0, ColOffset) > 0 Then
                 ActiveCell.Offset(0, 1).Select
                 ActiveCell = HolidayHours
                 ActiveCell.Offset(-1, 0).Select
               End If
             Next ColOffset
           Wend

         End With

[green]

'<--- inserted

[/color]

       End If

[green]

'<--- inserted

[/color]

     End With

[green]

'<--- inserted

[/color]

   End If

[green]

'<--- inserted

[/color]

 End If

[green]

'<--- inserted

[/color]

Loop Until ActiveCell = "" And ActiveCell.Offset(0, 1) = "" And ActiveCell.Offset(1, 0) = "" And ActiveCell.Offset(1, 1) = ""

[green]

'the last offset above seems to be part of the problem

[/color]

End Sub

[/color]

 

[willyboy58]

Zathras,

With your code I no longer get the Loop problem message.
I now need to work out the logic within my procedure. Let me see if I can get it on my own. If I get hung up again, I'll post.

Thanks for the help.

Bill