Loop Help Needed

[DaBeard]

I am looping through a named range (Log) to see if the cell value matches a variable that I enter an inputbox and then if an offset cell matches another variable that I enter in a second inputbox.

My problem is that even if the matches are found the Msgbox still pops up and I exit the sub. I want that to be the case if no match is found.

I have looked through posts and pages for two days now PLEASE HELP!!!!!

Code below:

Sub mtch()
Dim c As Range
Dim myVar1 As String
Dim myVar2 As String
'Set CSN of copied row as first variable
myVar1 = InputBox("Enter CSN Number of copied row")
'Set Req Line # of copied row as second variable
myVar2 = InputBox("Enter Req Line # of copied row")
   
'Searches through the range "Log" for the first variable and then checks to see if second variable matches, if TRUE selects the cell in Log
    For Each c In Range("Log")
        If c.Value = myVar1 And c.Offset(0, 26).Value = myVar2 Then
            Application.Goto c, True
                Exit For
        End If
    Next c
 
MsgBox "Incorrect CSN or Req Line # entered. Please try again."
    Exit Sub

I'm sure it is something simple that I just don't know about.

Thank you for any help,
DaBeard

 

[JLGWhiz]

Try moving the message box as shown below.

Sub mtch()
Dim c As Range
Dim myVar1 As String
Dim myVar2 As String
'Set CSN of copied row as first variable
myVar1 = InputBox("Enter CSN Number of copied row")
'Set Req Line # of copied row as second variable
myVar2 = InputBox("Enter Req Line # of copied row")
'Searches through the range "Log" for the first variable and then checks to see if second variable matches, if TRUE selects the cell in Log
    For Each c In Range("Log")
        If c.Value = myVar1 And c.Offset(0, 26).Value = myVar2 Then
            Application.Goto c, True
                Exit For
        Else
            MsgBox "Incorrect CSN or Req Line # entered. Please try again."
            Exit Sub
        End If
    Next c

 

[DaBeard]

Whiz,

Thanks for the reply.

I moved the MsgBox code as you suggested, but still encounter a problem. If the variables that I am looking for are the first cell/row of the named range offset column your suggestion works. But if the variables are located further down the range the code comes back as false and goes to the MsgBox. Mapped it out below

myVar1 = A
myVar2 = 1
"Log" Column Second Offset Column
A 1
B 1
C 2

Result: code works, no msgbox

But if:

myVar1 = B
myVar2 = 1
"Log" Column Second Offset Column
A 1
B 1
C 2

Result: MsgBox and then exits sub

I only want the MsgBox to appear if no match is found for the IF/THEN statment. Please advise.

Thanks again for any help provided!
DaBeard

 

[JLGWhiz]

Why not just make the message box a information alert and do away with the Exit Sub line, or re-write the code so that the logic allows you to make a determination of whether the correct data is being used?

 

[DaBeard]

Whiz,

Thanks for the reply. I have more code that comes after the potion I attached that I only want to run if the variable are found and they are in the same row. If they are not found in the same row I want the sub to end and the user to have to start over.

Thanks for any help.
DaBeard

 

[JLGWhiz]

Whiz,

Thanks for the reply. I have more code that comes after the potion I attached that I only want to run if the variable are found and they are in the same row. If they are not found in the same row I want the sub to end and the user to have to start over.

Thanks for any help.
DaBeard

Sorry to take so long in responding. I have been on an unrelated project and not monitoring the site so much.
I think to do what you want, which seems to be to exit the sub only if there is not match anywhere on the sheet, then you would need to use the Find method rather than the For...Next loop.

Sub mtch()
Dim fn As Range
Dim myVar1 As String
Dim myVar2 As String
'Set CSN of copied row as first variable
myVar1 = InputBox("Enter CSN Number of copied row")
'Set Req Line # of copied row as second variable
myVar2 = InputBox("Enter Req Line # of copied row")
'Searches through the range "Log" for the first variable and then checks to see if second variable matches, if TRUE selects the cell in Log
    Set fn = Range("Log").Find(myVar1, , xlValues, xlWhole)
        If Not fn Is Nothing Then
            If fn.Offset(0, 26) = myVar2 Then
                fn.Activate
            Else
                MsgBox "Incorrect Req Line # Entered.  Please try again."
                Exit Sub
            End If
        Else
            MsgBox "Incorrect CSN # Entered.  Please try again."
            Exit Sub
        End If

This would look for the first variable in Range("Log") and if found would then check for the second variable. If both are found, it would make the cell with the first variable the active cell. This will also allow the user to know which variable is the problem.

 

[DaBeard]

Whiz,

Thanks for the reply and help. Your solution is exactly where I ended up after talking with a collegue. My final code is below, please let me know if you see any possible snags.

Sub FindPair()
Dim myVar2 As String
'Set CSN of copied row as first variable
myVar1 = InputBox("Enter CSN Number of row to be copied")
'Set Req Line # of copied row as second variable
myVar2 = InputBox("Enter Req Line # of row to be copied")
    
    With Range("Log")
        Set c = .Find(myVar1, LookIn:=xlValues)
        If Not c Is Nothing Then
            c.Select
            ActiveCell.Offset(0, 26).Select
                    If ActiveCell.Value = myVar2 Then
                        c.Select
        Else
            MsgBox "Incorrect CSN or Req Ling # entered. Please try again."
                Exit Sub
                    End If
                    
        End If
    End With

Thanks for all of your help!

DaBeard

 

[JLGWhiz]

Whiz,

Thanks for the reply and help. Your solution is exactly where I ended up after talking with a collegue. My final code is below, please let me know if you see any possible snags.

Sub FindPair()
Dim myVar2 As String
'Set CSN of copied row as first variable
myVar1 = InputBox("Enter CSN Number of row to be copied")
'Set Req Line # of copied row as second variable
myVar2 = InputBox("Enter Req Line # of row to be copied")
    
    With Range("Log")
        Set c = .Find(myVar1, LookIn:=xlValues)
        If Not c Is Nothing Then
            c.Select
            ActiveCell.Offset(0, 26).Select
                    If ActiveCell.Value = myVar2 Then
                        c.Select
        Else
            MsgBox "Incorrect CSN or Req Ling # entered. Please try again."
                Exit Sub
                    End If
                    
        End If
    End With

Thanks for all of your help!

DaBeard

You're welcome,
Regards, JLG