for loop not finding fields that exist

[mdavid]

Hi, Need some help.
I'm trying to run the following code:

Private Sub update_lifeform()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim tempName As String
Dim lastRow1 As Long, lastRow2 As Long
Dim s2Row As Long, s1Row As Long
Dim SpeciesID As Range
Dim cpyCnt As Integer
cpyCnt = 0
Set sh1 = ActiveWorkbook.Worksheets("All Species")
Set sh2 = ActiveWorkbook.Worksheets("Berc LF Habitat Dist")
lastRow1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row
lastRow2 = sh2.Cells(Rows.Count, 2).End(xlUp).Row

For s1Row = 2 To lastRow1
'  If Not (sh1.Range("B" & s1Row) Is Nothing) Then
    For s2Row = 2 To lastRow2
     If sh1.Range("B" & s1Row).Value = sh2.Range("B" & s2Row) Then
             sh1.Range("O" & s1Row).Value = sh2.Range("G" & s2Row).Value
             sh1.Range("P" & s1Row).Value = sh2.Range("H" & s2Row).Value
             sh1.Range("Q" & s1Row).Value = sh2.Range("I" & s2Row).Value
             cpyCnt = cpyCnt + 1
     '        Exit For
       End If
     Next s2Row
'  End If
Next s1Row
Debug.Print "total species copied = " & cpyCnt
End Sub

Looks pretty straight forward, but the following If misses alot of the rows
If sh1.Range("B" & s1Row).Value = sh2.Range("B" & s2Row) Then

even though Debug.print "total species copied = " & cpyCnt
indicates all matching records where found
For example values:
994
993
805
700
937
and many more which exist in column "B" of both sheets are not recognized and the cells O, P & Q
are not updated.
Really appreciate if some one could tell me the error of my ways, I've attached the problem sheets
Thanks for any help
David

 

[Fluff13]

Rows 310 to 314 (which contain the values you flagged) get updated when I run your code.

 

[mdavid]

Hi, Thanks for taking the time to check this.
Do you have any idea why it's not working for me. The records that get updated seems to vary each time I run this code - what would be the recommended way to do this .Range , .Cells or .Find - I've tried each one and the results change each time - but I never succeed to update all matching records.

Thanks for you help
David

 

[Fluff13]

As your code works the same for me every time I run it, it's difficult to say why you get differing results.
Do you have any other code in that sheet, like a Change event?

 

[mdavid]

Hi,
I received a Windows 10 Update and tried it again after restarting Windows - and it's now working! So either the Windows update sorted the problem, or I'm going a little crazy.
Whichever - thanks for your help
David

 

[Fluff13]

Glad it's working for you & thanks for the feedback