0

I am attempting to write a macro that checks each row in one sheet called raw data for a matching name and if the name matches, copy the data from that row over to a sheet called name search. I am attempting to do this using a do while loop similar to one that i have used successfully in the past.

However when I try to run it, it gives me the error "Loop without do" despite the fact that everything seems to be in the correct place. my code is as follows:

Sub NameSearch()

Sheets("Raw Data").Unprotect ("29745")
Application.ScreenUpdating = False

Dim x As Long

'set starting point at row 2
x = 2

Dim sourceSheet As Worksheet: Set sourceSheet = ThisWorkbook.Worksheets("Raw Data")
Dim destSheet As Worksheet: Set destSheet = ThisWorkbook.Worksheets("Name Search")

Do While sourceSheet.range("A" & x).Value <> ""

    If sourceSheet.range("O" & x).Value <> destSheet.range("B2") Then
        x = x + 1

    Else
    If sourceSheet.range("O" & x).Value = destSheet.range("B2") Then

'selects the next row where the 1st column is empty
            lMaxRows = destSheet.Cells(destSheet.Rows.Count, "A").End(xlUp).Row
'pastes the data from the specified cells into the next empty row
            destSheet.range("A" & lMaxRows + 1).Value = sourceSheet.range("A" & x).Value
            destSheet.range("B" & lMaxRows + 1).Value = sourceSheet.range("B" & x).Value
            destSheet.range("C" & lMaxRows + 1).Value = sourceSheet.range("C" & x).Value
            destSheet.range("D" & lMaxRows + 1).Value = sourceSheet.range("D" & x).Value
            destSheet.range("E" & lMaxRows + 1).Value = sourceSheet.range("E" & x).Value
            destSheet.range("F" & lMaxRows + 1).Value = sourceSheet.range("F" & x).Value
            destSheet.range("G" & lMaxRows + 1).Value = sourceSheet.range("G" & x).Value
            destSheet.range("H" & lMaxRows + 1).Value = sourceSheet.range("F" & x).Value - sourceSheet.range("G" & x).Value
            destSheet.range("I" & lMaxRows + 1).Value = sourceSheet.range("M" & x).Value
            destSheet.range("J" & lMaxRows + 1).Value = sourceSheet.range("N" & x).Value

        x = x + 1

    End If

Loop

End Sub

I can not for the life of me figure out what I did wrong. Any help improving my code would be greatly appreciated!

2
  • 2
    You need another End IF Commented Jan 4, 2017 at 15:40
  • im blind thank you so mich Commented Jan 4, 2017 at 15:42

1 Answer 1

1

You are missing an End If ... see below

Sub NameSearch()

Sheets("Raw Data").Unprotect ("29745")
Application.ScreenUpdating = False

Dim x As Long

'set starting point at row 2
x = 2

Dim sourceSheet As Worksheet: Set sourceSheet = ThisWorkbook.Worksheets("Raw Data")
Dim destSheet As Worksheet: Set destSheet = ThisWorkbook.Worksheets("Name Search")

Do While sourceSheet.range("A" & x).Value <> ""

    If sourceSheet.range("O" & x).Value <> destSheet.range("B2") Then
        x = x + 1

    Else
    If sourceSheet.range("O" & x).Value = destSheet.range("B2") Then

'selects the next row where the 1st column is empty
            lMaxRows = destSheet.Cells(destSheet.Rows.Count, "A").End(xlUp).Row
'pastes the data from the specified cells into the next empty row
            destSheet.range("A" & lMaxRows + 1).Value = sourceSheet.range("A" & x).Value
            destSheet.range("B" & lMaxRows + 1).Value = sourceSheet.range("B" & x).Value
            destSheet.range("C" & lMaxRows + 1).Value = sourceSheet.range("C" & x).Value
            destSheet.range("D" & lMaxRows + 1).Value = sourceSheet.range("D" & x).Value
            destSheet.range("E" & lMaxRows + 1).Value = sourceSheet.range("E" & x).Value
            destSheet.range("F" & lMaxRows + 1).Value = sourceSheet.range("F" & x).Value
            destSheet.range("G" & lMaxRows + 1).Value = sourceSheet.range("G" & x).Value
            destSheet.range("H" & lMaxRows + 1).Value = sourceSheet.range("F" & x).Value - sourceSheet.range("G" & x).Value
            destSheet.range("I" & lMaxRows + 1).Value = sourceSheet.range("M" & x).Value
            destSheet.range("J" & lMaxRows + 1).Value = sourceSheet.range("N" & x).Value

        x = x + 1

    End If
End If '<----MISSING END IF
Loop

End Sub
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.