To Compare 2 Sets Of Data And Change The Values Accordingly

[alivenwell]

Hi I am a newbie to VBA, I am desinging a macro that involves the processing data in 2 timetables, they show deliveries for different countries.

TableCustomer shows the customer's name, country and their opening times, Saturday - Friday, it uses 1s and 0s to determine if they are open or closed, 1 = open, 0 = closed, so the headings are: Customer Name, Country, Saturday, Sunday, Monday, Tuesday, Wednesday etc.

TableCompany shows the days of deliveries made to each country by the company, using 1s and 0s, 1 = Deliver, 0 = Don't deliver. The headers are Country, Saturday, Sunday, Monday, Tuesday, Wednesday etc.

An example here is that, if in TableCustomer, the customer is opened(1) on a Saturday, but in TableCompany, the company does not deliver(0) to that country on the Saturday, change that 1 into a 0 in TableCustomer. But if the Customer is closed(0) on the Saturday, then don't change the value 0, because the company will not be able to deliver to them anyway, even if they can deliver to that country on that day.

At the end of the day, the macro should:
1. Find the Country from the customer table and match it with the company table
2. With the found row, find the values of Saturday - friday.
3. If the date values in the Customer table is 1, then let it be what the Company table on that day says, otherwise let it be 0.
4. Loop

Here is a part of my code that i have constructed, have i used the right method?


Do Until CustomerCountry1 = CompanyCountry1
Set CustomerCountryValue = .Cells(CustomerCountryRow + i, CustomerSatCol + d)
Set CustomerCountry1 = .Cells(CustomerCountryRow + i, CustomerCountryCol)
Set CustomerRngRow = .Range((.Cells(CustomerCountryRow + i, CustomerCountryCol)), (.Cells(CustomerCountryRow + i, CustomerFriCol)))
Set CustomerCountryRange = .Range((.Cells(CustomerCountryRow + i, CustomerCountryCol)), (.Cells(GLCustomerRow, CustomerCountryCol)))
Set CompanyCountry1 = .Cells(CompanyCountryRow + z, CompanyCountryCol)
Set CompanyCountryValue = .Cells(CompanyCountryRow + z, CompanySatCol + d)
'Set CompanySatvalue = Cells.Find("Saturday", ActiveCell, xlValues, xlPart, xlByRows, xlNext, False).Value
'Set CompanyFCountry = .Range((.Cells(CompanyCountryRow + z, CompanySatCol + d)), (.Cells(CompanyCountryRow + z, CompanyFriCol)))
Set Companyrngrow = .Range((.Cells(CompanyCountryRow + z, CompanyCountryCol)), (.Cells(CompanyCountryRow + z, CompanyFriCol)))
Set CompanyCountryRange = .Range((.Cells(CompanyCountryRow + z, CompanyCountryCol)), (.Cells(GLRow, CompanyCountryCol)))


If CustomerCountry1 = CompanyCountry1 Then
CustomerCountryValue = CompanyCountryValue
Else: CustomerCountryValue = 0
End If

i = i + 1
z = z + 1
d = d + 1

Loop

[YellowLabPro]

Can you post your sheet?

[alivenwell]

Here it is, this is a template of the sheets I am dealing with:
6757

[rory]

I confess I haven't looked at your sheet, but wouldn't it be easier to set up a third table that simply checks the 1s and 0s from the other two tables - if they are both 1, then enter 1, otherwise enter 0. You can then use this table to determine delivery options. It also means that if you change the values in one of the tables, the third table will update automatically.
Just a thought.

[alivenwell]

you need to take a look at the table, because there is more than one customer from a particular country, so you can't directly link it up with the Company table, which only has data for delivering to each country.

[rory]

I don't see how that's a problem. You would effectively have a replica of the Customers sheet that checks each customer row against the delivery options for that country.