Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Reluctant Macro
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default Reluctant Macro
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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Reluctant Macro
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.

--
Regards,
Tom Ogilvy

Steve wrote in message
...
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




Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default Reluctant Macro
It's not related to duplcates. Any other ideas? I'm stumped.

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

Steve wrote in message
...
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






Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro recorded... tabs & file names changed, macro hangs Steve Excel Worksheet Functions 3 October 30th 09 11:41 AM
Hyperlinks reluctant at intervals Tevuna Excel Discussion (Misc queries) 0 August 15th 07 02:50 AM
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort Gavin Excel Worksheet Functions 0 May 17th 07 01:20 PM
Macro needed to Paste Values and prevent Macro operation thunderfoot Excel Discussion (Misc queries) 1 June 11th 05 12:44 AM
Macro needed to Paste Values and prevent Macro operation thunderfoot Excel Discussion (Misc queries) 0 June 10th 05 03:38 PM


All times are GMT +1. The time now is 05:05 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement