Trouble with a Loop

[RLH]

My objective is to take information from sheet xxx if "start" is in cells(r,24) This will populate information on sheet mpo. when the information in entered from cells in (r, whatever), start should change to "paid" which doesn't work. I have invisioned two cases when the loop would stop. First is when cells(r,23), when r= 8 to 55. Second when sheet MPO gets to rr=31.
I get the first line on sheet MPO properly with the exception of the month & year. It should be Feb 2013 for the first and second line on MPO, as well as Mar 2013 for the 4th and 5th line.
Any advice would be great...I have been working on this loop issue for a couple of days now.

 

[Luke M]

Hi RLH,

There were quite a few issues with your macro. Need to pay attention on some of your If--ElseIf--EndIf structures, as well as when variables are getting changed. I've cleaned it up a bit, and added some comments for instruction. Loop appears to work now. I'm not quite sure how the Start/Paid thing is supposed to work, but everything is getting copied to MPO now. Hopefully you can work from here.

Sub Flying_MPO()

'This Macro will generate a pay order based on flying hours for the month.
'This will loop to check to see if "START" is in the remarks on sheet XXX


'Each variable needs a definition, can't just label the last one
'I'm not sure if all of these are really needed either

Dim mpoy As String, mpom As String, mpod As String, status As String, name As String, ssn As String
Dim year As Variant, rr As Long, r As Variant, mon As Variant, rr2 As Long
Dim YR As Long, start As Date, last As Date
Dim duty As String, ssnf As String, ssnm As String, ssnl As String


'Items that don't change during macro are defined as Constants
Const mpo As String = "MPO"
Const xxx As String = "XXX"

With Sheets(xxx)
    name = .Cells(2, 1)
    ssnf = .Cells(2, 6)
    ssnm = .Cells(2, 7)
    ssnl = .Cells(2, 8)
End With
' Check to see if paying ACIP off of sheet XXX
' ACIP = 0 stops the loop

'This has to be outside the loop
'It was getting permanently stuck before
rr = 11

Application.ScreenUpdating = False
'Just to make sure we're on this sheet. No other sheets will get selected
'With statements will refer to MPO, regular statements trace back to active sheet

Sheets(xxx).Select
With Sheets(mpo)
    .Cells(5, 5) = Format(Date, "dd-mmm-yyyy")
   
    For r = 8 To 55
           
        status = Cells(r, 2)
        YR = Cells(r, 28)
        mon = Cells(r, 27)
       
        'Better way of defining these dates
        start = DateValue("1-" & mon & "-" & YR)
        last = WorksheetFunction.EoMonth(start, 0)
       
        'change the remarks from start to paid
        Cells(r, 24) = "Paid"
           
   
        'start adding flight time to MPO on row 11 on MPO
       
        'duty = .Cells(1, 8)
        '.Cells(9, 7) = YR
   
   
        ssn = ssnf & ssnm & ssnl
        .Cells(rr, 1) = ssn
        .Cells(rr, 3) = name
   
        .Cells(rr, 5) = status & duty
        .Cells(rr, 6) = start
        .Cells(rr, 7) = last
       
   
        'next line on MPO
        rr2 = rr + 1
        .Cells(rr2, 1) = "////////////////////"
        .Cells(rr2, 3) = "/////////// LAST ENRTY //////////"
        .Cells(rr2, 5) = "/////////////////////////////////"
        .Cells(rr2, 6) = "/////////////////"
        .Cells(rr2, 7) = "/////////////////"
   
        'Increment the counter
        rr = rr + 1
        'terminate the loop if the MPO has no more rows available
        If rr2 = 31 Then Exit For
           
    Next r
    'We only do this once, so it's outside the loop
    .Cells(33, 5) = "CMS CASE #:"
    .Cells(34, 5) = "Date Opened:"
    .Cells(35, 5) = "Date Closed:"
   
    With .Range(.Cells(33, 5), .Cells(35, 5))
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlCenter
    End With
End With
Application.ScreenUpdating = True


End Sub