Array or Something in VBA
 

I am trying to use an array in VBA but I am not having much luck. This is
what I am looking at:
I am looking for a match between the Customer and the Date in a list of
Customers and dates. So say the Date and Customer are specified, now you want
to look through the listed data to find a match. If there is a match then you
need to determine if you want to overwirte the data. I have the MsgBox and
corresponding code for that the over write but I dont know how to identify
the dulipcate data without using a Do loop and ActiveCell.Offset which
requires too much time to execute. I was hoping to do this with an array but
I can't figure out how to get it to work.

A B
1 CustA 2/1/2007
2 CustB 2/2/2007
3 CustA 2/1/2007
4 CustB 2/6/2007
5 CustB 2/16/2007

Array or Something in VBA
 

why not just sort the list on customer and then date. Then loop up the list
from last row to the 2nd row and delete every row that matches the row above.

--
Regards,
Tom Ogilvy


"ConfusedVBA" wrote:

I am trying to use an array in VBA but I am not having much luck. This is
what I am looking at:
I am looking for a match between the Customer and the Date in a list of
Customers and dates. So say the Date and Customer are specified, now you want
to look through the listed data to find a match. If there is a match then you
need to determine if you want to overwirte the data. I have the MsgBox and
corresponding code for that the over write but I dont know how to identify
the dulipcate data without using a Do loop and ActiveCell.Offset which
requires too much time to execute. I was hoping to do this with an array but
I can't figure out how to get it to work.

A B
1 CustA 2/1/2007
2 CustB 2/2/2007
3 CustA 2/1/2007
4 CustB 2/6/2007
5 CustB 2/16/2007

Array or Something in VBA
 

I though about it and I have gotten the sort an delete to work but but I was
trying not to reorder the data. Do you have any thoughts on what I could try?
The Do Loop does work but this is a lot of data and it takes a while.

"Tom Ogilvy" wrote:

why not just sort the list on customer and then date. Then loop up the list
from last row to the 2nd row and delete every row that matches the row above.

--
Regards,
Tom Ogilvy


"ConfusedVBA" wrote:

I am trying to use an array in VBA but I am not having much luck. This is
what I am looking at:
I am looking for a match between the Customer and the Date in a list of
Customers and dates. So say the Date and Customer are specified, now you want
to look through the listed data to find a match. If there is a match then you
need to determine if you want to overwirte the data. I have the MsgBox and
corresponding code for that the over write but I dont know how to identify
the dulipcate data without using a Do loop and ActiveCell.Offset which
requires too much time to execute. I was hoping to do this with an array but
I can't figure out how to get it to work.

A B
1 CustA 2/1/2007
2 CustB 2/2/2007
3 CustA 2/1/2007
4 CustB 2/6/2007
5 CustB 2/16/2007

Array or Something in VBA
 

Put in a dummy column on the right side that has the current row position.
Sort the data. Do the loop that deletes duplicates. then sort the data
using the dummy column as the key (this restores the order). Delete the
dummy column.

--
Regards,
Tom Ogilvy


"ConfusedVBA" wrote:

I though about it and I have gotten the sort an delete to work but but I was
trying not to reorder the data. Do you have any thoughts on what I could try?
The Do Loop does work but this is a lot of data and it takes a while.

"Tom Ogilvy" wrote:

why not just sort the list on customer and then date. Then loop up the list
from last row to the 2nd row and delete every row that matches the row above.

--
Regards,
Tom Ogilvy


"ConfusedVBA" wrote:

I am trying to use an array in VBA but I am not having much luck. This is
what I am looking at:
I am looking for a match between the Customer and the Date in a list of
Customers and dates. So say the Date and Customer are specified, now you want
to look through the listed data to find a match. If there is a match then you
need to determine if you want to overwirte the data. I have the MsgBox and
corresponding code for that the over write but I dont know how to identify
the dulipcate data without using a Do loop and ActiveCell.Offset which
requires too much time to execute. I was hoping to do this with an array but
I can't figure out how to get it to work.

A B
1 CustA 2/1/2007
2 CustB 2/2/2007
3 CustA 2/1/2007
4 CustB 2/6/2007
5 CustB 2/16/2007