CODEWITHSUNDEEP
vbaexcel
Mitch
Mitch

Reputation: 1

IF statement within a loop

I need help figuring out what I am doing wrong. Keep in my mind im an amateur.

I have two Workbooks, Workbook A and Workbook B. In workbook A I have a value in cell a1 and workbook B i have lets say 10 sheets with values in column A and B.

I would like to match value from a1 in workbook A by looping through all sheets in workbook B, and then returning the address if where it found it. I then write an if statement if the value is found to stop the loop because I will later have it search another cell, though I got stuck so I haven't written that line of code yet.

Here is the code and Ill note where im stuck.

sub autocheck()

dim found as varient
dim I as integer
dim val as varient
set val=workbooks("A").worksheets("primary").range("a1").value

For I = 1 to 10
on error resume next
set found=workbooks("b").worksheets(i).range("a:b").match(val)
found= found.address
if isempty(found) then

next i

else 
exit for
end if 

end sub

Here I get an error saying can not have next with out for.

Any idea how I get around these? I apologize for the formatting.

Thank you all!

Upvotes: 0

Views: 53

Answers (2)

Shai Rado
Shai Rado

Reputation: 33672

Your code should have "screamed" a few times:

  • dim val as variant ? maybe as Variant.
  • You should (or trying) to use match, your syntax is closer to Find. Using Find should also include a scenario Find didn't find the result. In any way, you need to Set the Find function result to a Range object.
  • Your closing Next I should be after the End If, not in the middle.
  • Using on error resume next doesn't make your error go away, you are just "closing your eyes" hoping it will go away.
  • More notes in my code's comments below.

Modified Code

Option Explicit

Sub autocheck()

Dim found As Range
Dim foundAdress As String
Dim i As Long
Dim val As Variant

Set val = Workbooks("A").Worksheets("primary").Range("a1").Value

For i = 1 To 10

    Set found = Workbooks("b").Worksheets(i).Range("A:B").Find(what:=val)

    If Not found Is Nothing Then ' Find was successfull
        foundAdress = found.Address
        Exit For
    Else ' Find failed
        ' in your code do nothing, continue looping
    End If
Next i

End Sub

Upvotes: 1

Landy
Landy

Reputation: 187

The problem is the if statement is spanning outside the loop. You should just exit the loop with the if statment and keep it contained inside the for

    For I = 1 to 10
    on error resume next
    set found=workbooks("b").worksheets(i).range("a:b").match(val)
    found= found.address
    if isEmpty(found) then
    //do something
    else 
    exit for
    end if

    next i



end sub

Upvotes: 1

Related Questions