Loop without a Do error

[omnisautnihil]

Hello, I am just now beginning to learn how to write Macros and I have been working on one that is being used to search through one document for a value in another and labeling an adjacent cell with the counter. All of my research points to people who do not have EndIf used appropriately but I do not see how mine is similar. Can anyone see why my code won't compile with "Loop with no Do" error?

*****************************
Sub Sorting()

Windows("File1.xls").Activate
Dim Count As Integer
Count = -1
Dim gCount As Integer
gCount = 0
Dim ICount As Integer
ICount = Count - gCount

Range("D5").Select
Dim rFound As Range

Do Until Selection.Value = ""

Count = Count + 1
Range("D5").Offset(Count, 0).Select
If Selection.Value = "General" Then

gCount = gCount + 1
Loop
​

ElseIf Selection.Value = Selection.Value.Offset(-1, 0) Then

Loop
​

Else

Selection.Value.Copy
Windows("ReviewAide.xls").Activate
Set rFound = Columns(2).Find(What:=Selection.Value, After:=Range ("B832"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows,_ SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
​

If Not rFound Is Nothing Then

Selection.Offest(0, -1).Select = "Issue " & ICount
ActiveCell.FontStyle = "Bold"
Windows("File1.xls").Activate
​

Else

MsgBox (Selection.Value & "is not a valid ID.")
Exit Do
​

End If
​

End If
​

Loop
MsgBox ("DONE!")
End Sub

**************************************************

If something needs explanation just let me know, but I'm only looking for why it does not understand my Loop statements where I want to break inside the if statements.

 

[MrKowz]

Try this code, it should accomplish what you want:

Public Sub Sorting()
Dim wb1     As Workbook, _
    ws1     As Worksheet, _
    wb2     As Workbook, _
    ws2     As Worksheet, _
    gCount  As Long, _
    LR      As Long, _
    i       As Long, _
    rFound  As Range
    
Set wb1 = Workbooks("File1.xls")
Set ws1 = wb1.activeworksheet
Set wb2 = Workbooks("ReviewAide.xls")
wb2.Activate
Set ws2 = wb2.activeworksheet
wb1.Activate
LR = ws1.Range("D" & Rows.Count).End(xlUp).Row
For i = 5 To LR
    If ws1.Range("D" & i).Value <> "General" And ws1.Range("D" & i).Value <> ws1.Range("D" & i - 1).Value Then
        Set rFound = ws2.Columns(2).Find(What:=ws1.Range("D" & i).Value, After:=Range("B832"), LookIn:=xlValues)
        If Not rFound Is Nothing Then
            With rFound.Offset(0, -1)
                .Value = "Issue " & iCount
                iCount = iCount + 1
                .Font.Bold = True
            End With
        Else
            MsgBox ws1.Range("D" & i).Value & " is not a valid ID."
            Exit For
        End If
    End If
Next i
End Sub

To answer your question about ElseIf Selection.Value = Selection.Value.Offset(-1, 0) Then, the Offset function requires a range object as its parent. To make that statement compile, you need to have it read ElseIf Selection.Value = Selection.Offset(-1,0).Value.

 

[omnisautnihil]

Now I get the same error for the Selection.Value.Copy line. Should that be Selection.Copy?


Do Until Selection.Value = ""

Count = Count + 1
Range("D5").Offset(Count, 0).Select
If Selection.Value = "General" Then

gCount = gCount + 1
​

ElseIf Selection.Value <>Selection.Offset(-1, 0) Then

Selection.Value.Copy
Windows("ReviewAide.xls").Activate
Set rFound = Columns(2).Find(What:=Selection.Value, After:=Range ("B832"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows,_ SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
​

If Not rFound Is Nothing Then

Selection.Offest(0, -1).Select = "Issue " & ICount
ActiveCell.FontStyle = "Bold"
Windows("File1.xls").Activate
​

Else

MsgBox (Selection.Value & "is not a valid ID.")
Exit Do
​

End If
​

End If
​

Loop
MsgBox ("DONE!")
End Sub

 

[MrKowz]

Like the offset line, copy requires a range reference:

Selection.Copy

Also, when you get to the Find line, your code may not do what you want, as the "selection" has now changed. Selection refers to the cell that is currently selected. When you activate the second workbook, your selection is now the selected cell in that workbook. A workaround for this is to store the value of the selection in your first workbook as a variable.

 

[omnisautnihil]

Ok awesome! Thanks for all the help guys, next step...haha

The find function is a bit complicated for me but as I have it written, it would search for the value in the one document all down the B column and when found it would select the cell adjacent in the A column.

Also as I understand it the Find function is a bit backwards because it is evaluating a False. So I think I have it correctly written to write to a variable, the range where the found text is located. Then I try to write to that cell. Of course it isn't working so what is wrong as it is written:

ElseIf Selection.Value <> Selection.Offset(-1, 0) Then

Selection.Copy
Windows("ReviewAide.xls").Activate
Set rFound = Columns(2).Find(What:=Selection.Value, After:=Range("B832"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

If Not rFound Is Nothing Then

rFound.Offest(0, -1).Select
Selection.Value = "Issue " & ICount
ActiveCell.FontStyle = "Bold"
Windows("TMP-DPM-111.xls").Activate
​

Else

MsgBox (Selection.Value & "is not a valid SRS ID.")
Exit Do
​

End If
​

End If

 

[MrKowz]

In this line, the red-bolded part is no longer referring to your original "selection". Also, are you intending for it to look for values after B832 (meaning from B833 and down), or do you want it to include all of Column B?

Set rFound = Columns(2).Find(What:=[COLOR=red][B]Selection.Value[/B][/COLOR], After:=Range("B832"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False

In this case, we need to do one of two things:

1. Assign the Selection.Value to a variable that we can pass into the Find method
2. Directly refer to the original selection

To elaborate on both cases:

1)

    ElseIf Selection.Value <> Selection.Value.Offset(-1, 0) Then
        [COLOR=red][B]temp = Selection.Value[/B][/COLOR]
        Windows("ReviewAide.xls").Activate
        Set rFound = Columns(2).Find(What:=[COLOR=red][B]temp[/B][/COLOR], After:=Range("B832"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
 
        If Not rFound Is Nothing Then
            Selection.Offest(0, -1).Select = "Issue " & iCount
            ActiveCell.FontStyle = "Bold"
            Windows("File1.xls").Activate
 
        Else
            MsgBox (Selection.Value & "is not a valid ID.")
            Exit Do
        End If
    End If

2)

    ElseIf Selection.Value <> Selection.Value.Offset(-1, 0) Then
        Windows("ReviewAide.xls").Activate
        Set rFound = Columns(2).Find(What:=[COLOR=red][B]Workbooks("File1.xls").Sheets("Sheet1").Range("D5").Offset(Count, 0).Value[/B][/COLOR], After:=Range("B832"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
 
        If Not rFound Is Nothing Then
            Selection.Offest(0, -1).Select = "Issue " & iCount
            ActiveCell.FontStyle = "Bold"
            Windows("File1.xls").Activate
 
        Else
            MsgBox (Selection.Value & "is not a valid ID.")
            Exit Do
        End If
    End If

Note in this second example, it assumes that the sheet name you are originally running this code on is named "Sheet1".

 

[omnisautnihil]

you are really fast at coding...sheesh haha

Thanks for that catch, that wasnt the reason for my error, but I fixed it.

Now using a similar with statement that you had above, I'm receiving a "Object doesnt support this property or method" error.

If Not rFound Is Nothing Then

With rFound.Offest(0, -1)

.Value = "Issue " & ICount
.Font.Bold = True

​

End With
Windows("TMP-DPM-111.xls").Activate

​

Else

MsgBox (Selection.Value & "is not a valid SRS ID.")
Exit Do

​

End If
​

 

[MrKowz]

You likely never dimmed rFound as a range variable. So Excel is trying to directly pass the value found into rFound instead of the range reference (it is always best to explicitly declare all of your variables. To ensure you do this on future projects, I would recommend that you go to Tools>Options and tick the box for "Require Variable Declaration".)

Up at the top of your code where you have all of the Dim statements, add in there:

Dim rFound as Range

 

[omnisautnihil]

I did already do that. Also, I read somewhere that the after command was a misnomer and it actually signified where the search ended...

 

[omnisautnihil]

Here is the complete code as of now...

Sub Sorting()

Windows("File1.xls").Activate
Dim Count As Integer
Count = -1
Dim gCount As Integer
gCount = 0
Dim ICount As Integer
ICount = Count - gCount

Range("D5").Select
Dim rFound As Range

Do Until Selection.Value = ""

Count = Count + 1
Range("D5").Offset(Count, 0).Select
If Selection.Value = "General" Then

gCount = gCount + 1
​

ElseIf Selection.Value <> Selection.Value.Offset(-1, 0) Then

temp = Selection.Value
Selection.Value.Copy
Windows("ReviewAide.xls").Activate
Set rFound = Columns(2).Find(What:=temp, After:=Range ("B832"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows,_ SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
​

If Not rFound Is Nothing Then

With rFound.Offest(0, -1)

.Value = "Issue " & ICount
.Font.Bold = True
​

End With
Windows("File1.xls").Activate
​

Else

MsgBox (Selection.Value & "is not a valid ID.")
Exit Do
​

End If
​

End If
​

Loop
MsgBox ("DONE!")
End Sub

 

[MrKowz]

Not that I'm aware of ...

Try changing the entire block to:

    ElseIf Selection.Value <> Selection.Value.Offset(-1, 0) Then
        [COLOR=red][B]temp = Selection.Value[/B][/COLOR]
        Windows("ReviewAide.xls").Activate
        [COLOR=red][B]Set rFound = Columns(2).Find(What:=temp, LookIn:=xlValues)[/B][/COLOR]
        
        If Not rFound Is Nothing Then
            With rFound.Offest(0, -1)
                .Value = "Issue " & iCount
                .Font.Bold = True
            End With
            Windows("File1.xls").Activate
 
        Else
            MsgBox (Selection.Value & "is not a valid ID.")
            Exit Do
        End If
    End If

Ps - when posting code, please wrap it in [CODE][/CODE] tags. It lets us scroll through the code and retains indention when it is posted and also when it is copied into the VBA editor.