Match in two different sheet and offset

Question

I am writing the following code that finds a match from one worksheet (Sheet2) and pastes values into (sheet2).

So far the code targets those names that have "accepted" as offset values. it loops through looking for a match and displaying it. However i would like to also select the offset values and paste them in sheet1 if possible. THis is where I am getting confused please help, where to take my code from here?

Sheet1

Column a , b

5 Jim Accepted

6 Bob Rejected

7 Tim Accepted

Sheet 2

Column d e f g

  Jim      40              0.4
  Bob      78              58
  Tim      36              45  


Sub check()
Dim i As Long, lastrow As Long, myval As Long
Dim agentname As String
Dim sh2 As Worksheet
Dim val As String
Dim findstr As String
Dim rng As Range

Set sh2 = Sheets(2)

lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lastrow
agentname = Cells(i, 1).Offset(, 1).Value
If Cells(i, 1) = "Accepted" And Not IsEmpty(Cells(i, 1)) Then
    'For i = 1 To sh2.Range("b2:b9")

myval = Application.WorksheetFunction.Match(agentname, sh2.Range("b1:b9"), 0)
findstr = agentname

MsgBox agentname

End If
Next i

End Sub

Acantud Acantud · Accepted Answer · 2014-05-02T22:46:39

Few problems -

agentname = Cells(i, 1).Offset(, 1).Value

This appears to be looking in the wrong column. It's returning "Accepted" or "Rejected" instead of the name. So remove the offset - you can specify what column you want directly, no need to offset. Keep it simple. (You don't even need the .Value, it's assumed)

agentname = Cells(i,1)

Now this is doubly confusing--

If Cells(i, 1) = "Accepted" And Not IsEmpty(Cells(i, 1)) Then

Again referring to the wrong column, we want column 2 here. And beyond that, if it is 'accepted', how could it also be empy? So we want:

If Cells(i, 2) = "Accepted" then

Ok, so next -

myval = Application.WorksheetFunction.Match(agentname, sh2.Range("b1:b9"), 0)

What is myval? Not a descriptive name, that can confuse you when you come back to your code. You're trying to match the agent name you saved, and specifying the range and sheet is on, that's great. Are the names really in column B? Without knowing the exact layout i'll assume they're on sheet 2 column A instead, that's easier for me. So now it's working when it finds a name, but a weird function of Worksheetfunction means it'll break if it can't find anything. So we'll wrap it in some error handling.

On Error Resume Next
myval = Application.WorksheetFunction.Match(agentname, sh2.Range("A1:A9"), 0)
If Err = 0 Then
findstr = agentname
MsgBox agentname
End If
On Error GoTo 0

I'm a little short on time, so a few more comments-

1.) You named your worksheet2 , that's fantastic. You should do it for worksheet1 too. In fact, go further and specify the workbook too. It'll save a lot of headache.

2.) You need to indent properly, maybe it just messed up when you posted here but those For loops and If statements need to be indented or you'll get a headache the next time you try to figure out what you were doing

3.) Use descriptive names, especially if you're writing a longer script that does a lot of things like this one.

4.) This is an opinion, but I think you should wait to declare your variables until you're about to use them. Again increased readibility. (And look into camel-Case for naming your variables, it's cosmetic, but...anyway I think it looks better)

Setup I was using - Sheet1:

A     B
Tim   Accepted
Tom   Rejected

Sheet2:

A    B    C
Tim  40   30.1
Tom  21   15.5
Jeff 18   31.3

Code:

Sub check()

Dim sh1 As Worksheet: Dim sh2 As Worksheet
Set sh1 = Sheets(1)
Set sh2 = Sheets(2)

Dim val As String
Dim findstr As String

Dim lastrow As Long
lastrow = sh1.Cells(Rows.Count, "A").End(xlUp).Row

Dim i As Long
For i = 1 To lastrow

    Dim agentname As String
    agentname = sh1.Cells(i, 1)

    If sh1.Cells(i, 2) = "Accepted" Then
        On Error Resume Next

        Dim myval As String
        myval = Application.WorksheetFunction.Match(agentname, sh2.Range("A1:A9"), 0)

        If Err = 0 Then
            findstr = agentname
            MsgBox agentname
        End If

        On Error GoTo 0

    End If
Next i

End Sub

Match in two different sheet and offset

2 Answers