Good Day all,
I have to explain the process of what I am trying to put in an excel workbook to simplify a process which takes up too many hours of my day. I also need to point out that I have only recently joined Chandoos VBA classes.
I inherited a process (paper) when I started working for a company in Zimbabwe 3 years ago. I have just been given permission to change it (simplify) the whole process.
The process start as follows:
1. ICO generated on four (4) Estates. (ICO = Inter Company Order). This means 4 different estates send their orders to me.
2. An ICO can request up to 8 different items which needs to be bought from 8 different suppliers.
3. From the ICO's (can receive up to 10 a day - very busy day) I need to see what needs to be ordered. Email 3 different suppliers for the same thing (need to produce for audit purposes 3 different quotes).
4. Once the quote was accepted the order from several ICO's need to be written in a book under the suppliers name for tracking purposes as a order is seldom complete.
5. Place the order by generating an order form for that supplier. On the order form the ICO number and Estate that ordered the specific item has to be indicated for reference sake as all 4 Estates can order the same item.
6. Upon receiving the order I need to check that the correct order was received. Now in the same book where the order was written for tracking purposes I have to write the date of when I received the order to show if there is anything still outstanding that I need to follow up.
What I have done so far:
I created a workbook in which my first sheet is a list of Suppliers with abbreviations that I use to identify them. The second sheet is ICO where I will enter the ICo to copy and paste to the respective sheets.
Then I created a sheet each for the 4 Estates.
Then the long list of sheets per suppliers that I deal with.
I searched the net for help and stumbled across Chandoo's site. I found a VBA code that assist me with getting the information into the separate sheets. (I did sign up for the excel, dashboards and VBA classes).
There is still a lot I want and need to do before I can start using the workbook. The problem I am facing now is that I want to on the supplier sheets strikethrough the orders when it is complete by checking the received date column. If there is a date in the column, I want cells a:h in the same row to be struck through so that I can see at a glance what is still outstanding. I also dont want the information to be deleted as I need to refer to it when need be.
I have tried to use that which I have learned already at Chandoo but I am failing. I can get part of it to work but its not moving to the next row to check and apply the condition.
I can get the Do While loop to work, the If statement to work but I dont know how to tell excel to apply the code to the next range.
I have uploaded the worksheet (NOT COMPLETED) as a sample.
If one of the capable Ninjas can have a look and let me know what I am doing wrong.
Thanks in advance.
AlwenaB
I have to explain the process of what I am trying to put in an excel workbook to simplify a process which takes up too many hours of my day. I also need to point out that I have only recently joined Chandoos VBA classes.
I inherited a process (paper) when I started working for a company in Zimbabwe 3 years ago. I have just been given permission to change it (simplify) the whole process.
The process start as follows:
1. ICO generated on four (4) Estates. (ICO = Inter Company Order). This means 4 different estates send their orders to me.
2. An ICO can request up to 8 different items which needs to be bought from 8 different suppliers.
3. From the ICO's (can receive up to 10 a day - very busy day) I need to see what needs to be ordered. Email 3 different suppliers for the same thing (need to produce for audit purposes 3 different quotes).
4. Once the quote was accepted the order from several ICO's need to be written in a book under the suppliers name for tracking purposes as a order is seldom complete.
5. Place the order by generating an order form for that supplier. On the order form the ICO number and Estate that ordered the specific item has to be indicated for reference sake as all 4 Estates can order the same item.
6. Upon receiving the order I need to check that the correct order was received. Now in the same book where the order was written for tracking purposes I have to write the date of when I received the order to show if there is anything still outstanding that I need to follow up.
What I have done so far:
I created a workbook in which my first sheet is a list of Suppliers with abbreviations that I use to identify them. The second sheet is ICO where I will enter the ICo to copy and paste to the respective sheets.
Then I created a sheet each for the 4 Estates.
Then the long list of sheets per suppliers that I deal with.
I searched the net for help and stumbled across Chandoo's site. I found a VBA code that assist me with getting the information into the separate sheets. (I did sign up for the excel, dashboards and VBA classes).
There is still a lot I want and need to do before I can start using the workbook. The problem I am facing now is that I want to on the supplier sheets strikethrough the orders when it is complete by checking the received date column. If there is a date in the column, I want cells a:h in the same row to be struck through so that I can see at a glance what is still outstanding. I also dont want the information to be deleted as I need to refer to it when need be.
I have tried to use that which I have learned already at Chandoo but I am failing. I can get part of it to work but its not moving to the next row to check and apply the condition.
I can get the Do While loop to work, the If statement to work but I dont know how to tell excel to apply the code to the next range.
I have uploaded the worksheet (NOT COMPLETED) as a sample.
If one of the capable Ninjas can have a look and let me know what I am doing wrong.
Thanks in advance.
AlwenaB