Error Handler

[alivenwell]

I'm a newbie to vba and was coding this.

There are two files. One is the master file and the second is a country file.
The master files contains customers info and country they are based in. The country file contains a timetable consisting or 1s and 0s. 1 meaning closed and 0 meaning open, headers are Saturday - Friday, with the country column on the side.

My code attempts to find a country in the country file, according to the master file, and copies and pastes the timetable of that country row into the master file, to ensure that products are only delivered according to the country file. The master file has a range of dates on the top. Saturday - Friday over a period of 16 weeks. In my code the Y=1 to 16 is that bit, so the same timetable of the found country is pasted 16 times.

Problem: When it finds a country in the Master file, but cannot find it in the Country file, it displays an error message, is there a error handler that i can input into this code so that it skips that country and goes to the next country? From x = 7 To lastrow , example if the first country( where the row, x = 7) cannot be found then make x into 8 or something?

I've tried the On Error Resume Next handler....doesn't work for me?


For x = 7 To lastrow (1st country - last in Master file)
If Range("D" & x).Value <> "" Then
country = Range("D" & x).Value
colnum = 12 (where the first Saturday is on the Master file)
For y = 1 To 16 (copying and pasting the same times 16 times)
Workbooks(countrywb).Activate
On Error Resume Next
Cells.Find(country).Select
Range("C" & ActiveCell.Row, "I" & ActiveCell.Row).Copy
shtmaster.Activate
Cells(x, colnum).PasteSpecial xlPasteValues
Application.CutCopyMode = False
colnum = colnum + 7
Next
End If
Next
End With



Can someone help me please?

Thanks

[Bob Phillips]

Untested!



Dim ctyFind As Range
For x = 7 To lastrow '(1st country - last In Master file)
If Range("D" & x).Value <> "" Then
country = Range("D" & x).Value
colnum = 12 '(where the first Saturday Is on the Master file)
On Error Resume Next
Set ctyFind = Nothing
Set ctyFind = Cells.Find(country)
If Not ctyFind Is Nothing Then
For y = 1 To 16 '(copying And pasting the same times 16 times)
Workbooks(countrywb).Activate
Range("C" & ctyFind.Row, "I" & ctyFind.Row).Copy
shtmaster.Activate
Cells(x, colnum).PasteSpecial xlPasteValues
Application.CutCopyMode = False
colnum = colnum + 7
Next
End If
End If
Next
End With

[alivenwell]

Doesn't work, where it says ctyFind.Row, it finds the row number of the country in the master file and copies accordingly from the country file. For example. The country it finds in Master file is France, and France is not on the Country file. From xld's code, the bit saying, ctyFind.Row. Finding France in the master file, it's row number is 14, however, cos ctyFind.Row is using the row number to copy, it uses row number "14" and from the Customer file, row 14 happens to be something else. Any more suggestions?

Thanks

[Bob Phillips]

Probably because i moved the Find up and ignored the fact you were activating the country wb. I assume it is sheet 1 in the country wb?



Dim ctyFind As Range
For x = 7 To lastrow '(1st country - last In Master file)
If Range("D" & x).Value <> "" Then
country = Range("D" & x).Value
colnum = 12 '(where the first Saturday Is on the Master file)
On Error Resume Next
Set ctyFind = Nothing
Set ctyFind = Workbooks(countrywb).Worksheets(1).Cells.Find(country)
If Not ctyFind Is Nothing Then
For y = 1 To 16 '(copying And pasting the same times 16 times)
Workbooks(countrywb).Activate
Range("C" & ctyFind.Row, "I" & ctyFind.Row).Copy
shtmaster.Activate
Cells(x, colnum).PasteSpecial xlPasteValues
Application.CutCopyMode = False
colnum = colnum + 7
Next
End If
End If
Next
End With

[alivenwell]

works THANKS!!:rotlaugh: !