• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

for loop not finding fields that exist

mdavid

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

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
 

Attachments

Last edited by a moderator:

mdavid

Member
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

Active Member
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

Member
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
 
Top