Stargazer
07-16-2010, 05:13 AM
Hi,
I've spent a long time lurking around the knowledge base picking up great little tidbits of code to use and it's helping me to learn more and more about VBA evetry time. So a big thanks to all the contributors!
But it's finally time for me to break my forum virginity here as I've run into a problem and require assistance.
I have a large block of data that contains customer contract information from our retail group. Three important fields we need to work with are IMEI number, SIM number and MPN (Mobile Phone Number).
Because of the way our point of sale system works, when a branch captures a sale, the transaction is divided up to either 2 or more lines, depending on how many accessories the customer buys at the same time.
The stupid POS system though, is putting the IMEI on one line and the SIM and MPN on one of the other lines. We have a ridiculous If statement that sort of corrects this but its not ideal.
The real issue though, is that we're concerned that possibly a branch may have stuffed up the sale and not generated one or more of these important bits of data.
What we need is a way for Excel to step through the data block, acknowledge that lines 2, 3, 4 & 5 are for customer Smith so it then checks that sub-block for the relevant criteria. It then needs to acknowledge that lines 6 & 7 are for customer Jones and check fro relevant criteria, and so on.
I'm trying to think of all the times I've used the 'introw' and 'lLastrow' commands in VBA, but am struggling to figure out how to make excel do what I'm thinking.
Perhaps there's an easier way. Perhaps there's a really tragically difficult way. But if there is a way, I'd really like to hear about it.
I hope I've explained this clearly enough. If you need any additional perspective then let me know and I'll see if I can provide.
Many thanks in advance,
Rob.
PS. I'm running Office 2007 in WinXP SP3. Don;t know how relevant that it but figured it couldn't hurt to mention it.
I've spent a long time lurking around the knowledge base picking up great little tidbits of code to use and it's helping me to learn more and more about VBA evetry time. So a big thanks to all the contributors!
But it's finally time for me to break my forum virginity here as I've run into a problem and require assistance.
I have a large block of data that contains customer contract information from our retail group. Three important fields we need to work with are IMEI number, SIM number and MPN (Mobile Phone Number).
Because of the way our point of sale system works, when a branch captures a sale, the transaction is divided up to either 2 or more lines, depending on how many accessories the customer buys at the same time.
The stupid POS system though, is putting the IMEI on one line and the SIM and MPN on one of the other lines. We have a ridiculous If statement that sort of corrects this but its not ideal.
The real issue though, is that we're concerned that possibly a branch may have stuffed up the sale and not generated one or more of these important bits of data.
What we need is a way for Excel to step through the data block, acknowledge that lines 2, 3, 4 & 5 are for customer Smith so it then checks that sub-block for the relevant criteria. It then needs to acknowledge that lines 6 & 7 are for customer Jones and check fro relevant criteria, and so on.
I'm trying to think of all the times I've used the 'introw' and 'lLastrow' commands in VBA, but am struggling to figure out how to make excel do what I'm thinking.
Perhaps there's an easier way. Perhaps there's a really tragically difficult way. But if there is a way, I'd really like to hear about it.
I hope I've explained this clearly enough. If you need any additional perspective then let me know and I'll see if I can provide.
Many thanks in advance,
Rob.
PS. I'm running Office 2007 in WinXP SP3. Don;t know how relevant that it but figured it couldn't hurt to mention it.