alivenwell
09-07-2007, 03:56 AM
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
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