VBA Excel: If statement not working

Question

I'm attempting to sort some information on a sheet in an excel document into different groups using information from another sheet with both sheets having a column to identify them. Specifically a game is being played by men and women and I want to sort the results of the game in the second sheet into three columns; overall, men and women but player information is kept in the first sheet with the only identifying feature in the results sheet being their unique ID number which is in the first sheet as well. So basically I have two sheets that look like this:

Sheet 1:

      A      |   B   | C | D
   Name      | Gender|   | ID
Alex         |   M   |   | 171 
Alexis       |   F   |   | 172 
Kelly        |   F   |   | 177
Chris        |   M   |   | 179

Sheet 2:

  A    | B | C | D
 ID    |   |   | Score
 171   |   |   | 58.2 
 172   |   |   | 67.1
 177   |   |   | 73.4
 179   |   |   | 68.95

Right now I'm just trying to get it to work so that all IDs and scores identified as male copied to another sheet, for this I have two different solutions but neither work.

Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim sh3 As Worksheet
Dim lc1, lc2, x, y, i, vLook, z

Set sh1 = Sheets("players")
Set sh2 = Sheets("Results")
Set sh3 = Sheets("temp")

rcount1 = sh1.Cells(Rows.Count, "A").End(xlUp).Row
rcount2 = sh2.Cells(Rows.Count, "A").End(xlUp).Row

x = 2
y = 2
z = 2




Dim t As Integer
Dim k As Integer
k = 1
t = 1


For t = 1 To rcount2

If sh2.Range("A1").Offset(t).Value = sh1.Range("D1").Offset(t).Value Then
If sh1.Range("B1").Offset(t).Value = "M" Then


sh3.Range("A1").Offset(k).Value = sh2.Range("A1").Offset(t).Value
sh3.Range("B1").Offset(k).Value = sh2.Range("D1").Offset(t).Value

k = k + 1
End If
End If

Next t

If I remove the 'if' statements then the ranges are copied over but with the 'if' statements it does nothing.

My other solution is this:

For i = 2 To rcount2
   vLook = Application.WorksheetFunction.VLookup(sh1.Cells(i, 4), Range(sh2.Cells(1, 1), sh2.Cells(rcount2, 4)), 4, "false")
    If sh1.Cells(i, 2) = "M" Then
        sh3.Cells(x, 1) = sh1.Cells(i, 4)
        sh3.Cells(x, 2) = vLook
        x = x + 1
    ElseIf sh1.Cells(i, 2) = "F" Then
        sh3.Cells(y, 3) = sh1.Cells(i, 4)
        sh3.Cells(y, 4) = vLook
        y = y + 1
        Else
        sh3.Cells(z, 5) = sh1.Cells(i, 4)
        sh3.Cells(z, 6) = vLook
        z = z + 1
    End If
Next i

But here all it does is treat everything as if they only fit into 'else' So basically as far as I can see, it's not seeing anything in the B column of of sheet 1 as M or F. Any help or suggestions would be appreciated.

Answer 1

i just based this on the info you provided above.
This code copies all Male ID and scores to Temp sheet in Colums A and B respectively.

Dim rcount1, rcount2, t as long
Dim sh1, sh2, sh3 as Worksheet
Dim wb as Workbook
Dim score

Set wb = Thisworkbook 'i included wb for flexibility, your call if you want to adopt
Set sh1 = wb.Sheets("Players")
Set sh2 = wb.Sheets("Results")
Set sh3 = wb.Sheets("Temp") 

rcount1 = sh1.Cells(Rows.Count, "A").End(xlUp).Row

For t = 2 to rcount1
    If sh1.Range("B" & t).Value Like "*M*" Then
        'rcount2 should be inside your loop, otherwise you'll only overwrite values
        rcount2 = sh3.Cells(Rows.Count, "A").End(xlUp).Row
        sh1.Range("D" & t).copy sh3.Range("A" & rcount2 + 1)
        'I used Vlookup function instead of nesting another loop
        With Application.WorksheetFunction
            score = .Vlookup(sh1.Range("D" & t).Value, sh2.Columns("A:D"), 4, 0)
            sh3.Range("B" & rcount2 + 1).value = score
        End with
    End if
Next t

End Sub

So this code is like combination of your works above.
Hope this get's you started.

This code only consolidates the information from "Players" and "Results" sheet.
Meaning, it doesn't check if an ID already exist in "Temp" sheet.
Also, it doesn't summarize.
I leave the rest to you.