Reluctant Macro

[Steve]

The macro below works sometimes, sometimes it doesn't. I can run it and some
cells will be updated and others that should have been updated aren't. I can
run it a second time and it'll update some of those cells that didn't get
updated the first time. What's going on here? Am I not clearing some sort of
buffer or what? Please help - people are waiting on a fix and I don't have
a clue.



Thanks







Sub Netcheck()

Application.StatusBar = "Processing crosscheck Macro"

MYFILE1 = "List of customers.xls"

Myfile2 = "My other list of customers.xls"



CcNC1 MYFILE1, Myfile2

CcNC2 MYFILE1, Myfile2

CcNC3 MYFILE1, Myfile2

CcNC4 MYFILE1, Myfile2

CcNC5 MYFILE1, Myfile2

CcNC6 MYFILE1, Myfile2

CcNC7 MYFILE1, Myfile2

CcNC8 MYFILE1, Myfile2



CcNC10 MYFILE1, Myfile2

CcNC11 MYFILE1, Myfile2

CcNC12 MYFILE1, Myfile2

Application.StatusBar = "Macro completed"

MsgBox "Macro completed"

Application.StatusBar = ""

End Sub





'-------------------------- Batch 1 -------------------------------

Sub CcNC1(MYFILE1, Myfile2)

Application.StatusBar = "Processing Batch 1"

For Each strname In Workbooks(MYFILE1).Sheets(1).Range("M2:M10000")

With Workbooks(Myfile2).Sheets("Batch 1").Range("I2:I10000")

Set c = .Find(strname, LookIn:=xlValues, lookat:=xlWhole)

If Not c Is Nothing Then

If strname.Offset(0, -12) = "MYFILE1101" Then

c.Offset(0, 35) = strname.Offset(0, 24)

With c.Offset(0, 35).Interior

.ColorIndex = 6

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

End With

End If

End If

End With

Next

End Sub

'-------------------------- Batch 2 -------------------------------

Sub CcNC2(MYFILE1, Myfile2)

Application.StatusBar = "Processing Batch 2"

For Each strname In Workbooks(MYFILE1).Sheets(1).Range("M2:M10000")

With Workbooks(Myfile2).Sheets("Batch 2").Range("I2:I10000")

Set c = .Find(strname, LookIn:=xlValues, lookat:=xlWhole)

If Not c Is Nothing Then

If strname.Offset(0, -12) = "MYFILE1102" Then

c.Offset(0, 34) = strname.Offset(0, 24)

With c.Offset(0, 34).Interior

.ColorIndex = 6

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

End With

End If

End If

End With

Next

End Sub

'-------------------------- Batch 3 -------------------------------

Sub CcNC3(MYFILE1, Myfile2)

Application.StatusBar = "Processing Batch 3"

For Each strname In Workbooks(MYFILE1).Sheets(1).Range("M2:M10000")

With Workbooks(Myfile2).Sheets("Batch 3").Range("I2:I10000")

Set c = .Find(strname, LookIn:=xlValues, lookat:=xlWhole)

If Not c Is Nothing Then

If strname.Offset(0, -12) = "MYFILE1103" Then

c.Offset(0, 34) = strname.Offset(0, 24)

With c.Offset(0, 34).Interior

.ColorIndex = 6

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

End With

End If

End If

End With

Next

End Sub

'-------------------------- Batch 4 -------------------------------

Sub CcNC4(MYFILE1, Myfile2)

Application.StatusBar = "Processing Batch 4"

For Each strname In Workbooks(MYFILE1).Sheets(1).Range("M2:M10000")

With Workbooks(Myfile2).Sheets("Batch 4").Range("I2:I10000")

Set c = .Find(strname, LookIn:=xlValues, lookat:=xlWhole)

If Not c Is Nothing Then

If strname.Offset(0, -12) = "MYFILE1104" Then

c.Offset(0, 34) = strname.Offset(0, 24)

With c.Offset(0, 34).Interior

.ColorIndex = 6

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

End With

End If

End If

End With

Next

End Sub

'-------------------------- Batch 5 -------------------------------

Sub CcNC5(MYFILE1, Myfile2)

Application.StatusBar = "Processing Batch 5"

For Each strname In Workbooks(MYFILE1).Sheets(1).Range("M2:M10000")

With Workbooks(Myfile2).Sheets("Batch 5").Range("H2:H10000")

Set c = .Find(strname, LookIn:=xlValues, lookat:=xlWhole)

If Not c Is Nothing Then

If strname.Offset(0, -12) = "MYFILE1105" Then

c.Offset(0, 33) = strname.Offset(0, 24)

With c.Offset(0, 33).Interior

.ColorIndex = 6

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

End With

End If

End If

End With

Next

End Sub

'-------------------------- Batch 6 -------------------------------

Sub CcNC6(MYFILE1, Myfile2)

Application.StatusBar = "Processing Batch 6"

For Each strname In Workbooks(MYFILE1).Sheets(1).Range("M2:M10000")

With Workbooks(Myfile2).Sheets("Batch 6").Range("H2:H10000")

Set c = .Find(strname, LookIn:=xlValues, lookat:=xlWhole)

If Not c Is Nothing Then

If strname.Offset(0, -12) = "MYFILE1106" Then

c.Offset(0, 37) = strname.Offset(0, 24)

With c.Offset(0, 37).Interior

.ColorIndex = 6

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

End With

End If

End If

End With

Next

End Sub

'-------------------------- Batch 7 -------------------------------

Sub CcNC7(MYFILE1, Myfile2)

Application.StatusBar = "Processing Batch 7"

For Each strname In Workbooks(MYFILE1).Sheets(1).Range("M2:M10000")

With Workbooks(Myfile2).Sheets("Batch 7").Range("G2:G10000")

Set c = .Find(strname, LookIn:=xlValues, lookat:=xlWhole)

If Not c Is Nothing Then

If strname.Offset(0, -12) = "MYFILE1107" Then

c.Offset(0, 37) = strname.Offset(0, 24)

With c.Offset(0, 37).Interior

.ColorIndex = 6

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

End With

End If

End If

End With

Next

End Sub

'-------------------------- Batch 8 -------------------------------

Sub CcNC8(MYFILE1, Myfile2)

Application.StatusBar = "Processing Batch 8"

For Each strname In Workbooks(MYFILE1).Sheets(1).Range("M2:M10000")

With Workbooks(Myfile2).Sheets("Batch 8").Range("G2:G10000")

Set c = .Find(strname, LookIn:=xlValues, lookat:=xlWhole)

If Not c Is Nothing Then

If strname.Offset(0, -12) = "MYFILE1108" Then

c.Offset(0, 37) = strname.Offset(0, 24)

With c.Offset(0, 37).Interior

.ColorIndex = 6

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

End With

End If

End If

End With

Next

End Sub

'--------- Batch 9 doesn't have the Site Conversion Date -----------

'-------------------------- Batch 10 -------------------------------

Sub CcNC10(MYFILE1, Myfile2)

Application.StatusBar = "Processing Batch 10"

For Each strname In Workbooks(MYFILE1).Sheets(1).Range("M2:M10000")

With Workbooks(Myfile2).Sheets("Batch 10").Range("E2:E10000")

Set c = .Find(strname, LookIn:=xlValues, lookat:=xlWhole)

If Not c Is Nothing Then

If strname.Offset(0, -12) = "MYFILE1110" Then

c.Offset(0, 37) = strname.Offset(0, 24)

With c.Offset(0, 37).Interior

.ColorIndex = 6

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

End With

End If

End If

End With

Next

End Sub

'-------------------------- Batch 11 -------------------------------

Sub CcNC11(MYFILE1, Myfile2)

Application.StatusBar = "Processing Batch 11"

For Each strname In Workbooks(MYFILE1).Sheets(1).Range("M2:M10000")

With Workbooks(Myfile2).Sheets("Batch 11").Range("K2:K10000")

Set c = .Find(strname, LookIn:=xlValues, lookat:=xlWhole)

If Not c Is Nothing Then

If strname.Offset(0, -12) = "MYFILE1111" Then

c.Offset(0, 34) = strname.Offset(0, 24)

With c.Offset(0, 34).Interior

.ColorIndex = 6

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

End With

End If

End If

End With

Next

End Sub

'-------------------------- Batch 12 -------------------------------

Sub CcNC12(MYFILE1, Myfile2)

Application.StatusBar = "Processing Batch 12"

For Each strname In Workbooks(MYFILE1).Sheets(1).Range("M2:M10000")

With Workbooks(Myfile2).Sheets("Batch 12").Range("I2:I10000")

Set c = .Find(strname, LookIn:=xlValues, lookat:=xlWhole)

If Not c Is Nothing Then

If strname.Offset(0, -12) = "MYFILE1112" Then

c.Offset(0, 36) = strname.Offset(0, 24)

With c.Offset(0, 36).Interior

.ColorIndex = 6

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

End With

End If

End If

End With

Next

End Sub

 

[Tom Ogilvy]

You are only doing one find on each strname. You then check some
conditions and if not satisfied, you are done. If you are satisfied, you
make a change. If you only have one occurance of strname then this would
not be a problem, but I suspect you have multiple. You need use the help
example for the find or findnext method (xl2000 and earlier) to see how to
find all instances of a strname in your search range.

 

[Steve]

It's not related to duplcates. Any other ideas? I'm stumped.