Thread: Macro ignoring Loop

I have writtent the following macro, which is called in a larger macro. It is supposed to search a "Data" worksheet for names which do not appear in a "Staff List" worksheet in another spreadsheet, and add any missing names to the "Staff List" worksheet. However, when I run this macro, it will find the first missing name, add it appropriately, and then end the macro, ignoring the loop. There are many missing names which it should be finding, but it only finds one every time it runs. It is a different one every time. Any suggestions?

Sub Namecheck2()

' ' ' 'Create two worksheets to be referenced

ActiveSheet.name = "Data"
Sheets.Add
ActiveSheet.name = "Temp"

' ' ' 'Copy a list of staff from another spreadsheet

    Windows("Staff List.xls").Activate
    Sheets("Staff List").Select
    Cells.Select
    Selection.Copy
    Windows("test23.xls").Activate
    Sheets("Temp").Select
    Cells.Select
    ActiveSheet.Paste

' ' ' 'Go through every line in the "Data" worksheet, and if the value in the name column is not found in the "temp" worksheet, notify the user, and add the name and default salary information to the "Staff List" worksheet in another spreadsheet.

Sheets("Data").Select

Rows("2:2").Select


Do While ActiveCell.Offset(0, 0).Range("A1") <> ""

If (Application.WorksheetFunction.Lookup((ActiveCell.Offset(0, 0).Range("A1")), Sheets("Temp").Range("A2:A10000"))) <> ActiveCell.Offset(0, 0).Range("A1") Then

MsgBox (ActiveCell.Offset(0, 0).Range("A1") & " was not listed in the Staff List worksheet.  This name has been added to the Staff List spreadsheet, but you must manually enter his or her salary information manually.")

name = ActiveCell.Offset(0, 0).Range("A1")

    Windows("Staff List.xls").Activate
    Sheets("Staff List").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    ActiveCell = name
    ActiveCell.Offset(0, 1).Select
    ActiveCell = "Annual"
    ActiveCell.Offset(0, 1).Select
    ActiveCell = "=IF(RC[-1]=""Annual"",1,IF(RC[-1]=""Bi-Weekly"",26,IF(RC[-1]=""Hourly"",2080,IF(RC[-1]=""Academic"",4/3,""ERROR""))))"
    ActiveCell.Offset(0, 1).Select
    ActiveCell = 1
    ActiveCell.Offset(0, 1).Select
    ActiveCell = "=RC[-1]*RC[-2]"
    Windows("test23.xls").Activate
    Sheets("Temp").Select

End If

ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select

Loop

' ' ' 'Sort the data in the "Staff List" spreadsheet so it can be referenced in a VLOOKUP

    Windows("Staff List.xls").Activate
    Sheets("Staff List").Select
    Cells.Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
        , Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
    Windows("test23.xls").Activate
    Sheets("Temp").Select

Sheets("Temp").Delete

End Sub

Welcome to to the forum, chcwebb.

Please take a few minutes to read the forum rules, and then edit your post to add code tags.

Thanks.

Your code does exactly what you have it coded to do - It loops once

Within your loop you select the last used cell

Selection.End(xlDown).Select

You then select the next row and populate that with formulas

Before the Loop command at the end of your Do Loop you then select the next row down which is blank

ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select

P.S Macros can be coded do that you do not need to select cell all the time

Can you post a copy of the book - Make sure any sensitve data is changed