loop through a spreadsheet looking for the value from a userform combobox

[alexmech]

Hi Guys,

I have set up a spreadsheet to record transactions and modify them.

I have a few user forms to :
- Create a new transaction
- Extend the date
- deliver a transaction
- cancel a transaction.

All of these basically create a new record (row) with the new/updated data for a given transaction.

All rows start with the transaction reference (a number in column "A"). the transaction will keep that same number throughout.

All the creation of new record works well. But before creating a new record for an existing transaction I want to modify all other records of that transaction and change their status to "closed" (the status is recorded in column "B"). I can then filter in a separate tab to only see the "open" transactions, which works well.

I am basically trying to loop through the database (in spreadsheet "Blotter") looking for the reference of the transaction I am modifying (retrieved form a combobox in a userform) and update its status to "closed" (there may be several line items with the same reference but only one will have a status "Open") before I create a new record for that transaction with the status "open".

This is the code I have on validation of a transaction modifying an existing deal (it then calls "populate" which creates the new record and works well).

Please Login or Register  to view this content.

For some reason the status is stubborn and does not want to change to "Closed"...

Any help would be great...

Thanks

Alex

[Leith Ross]

Hello alexmech,

Welcome to the Forum!

Try this version of your macro.

Please Login or Register  to view this content.

[alexmech]

Excellent!...

I had to play with it a bit... I think there was a problem with the "capture" of the value from the Combobox, as your code initially didn't "close" the deal.
It's a bit of a "Barbaric" method but this is what I have done :

Please Login or Register  to view this content.

So I "paste" the variable in the spreadsheet and compare it to the range... not very aesthetic but it works...

Thanks for the help !

Cheers,

Alex

[Leith Ross]

Hello Alex,

Your approach is a reasonable one, certainly not barbaric. Glad I could help out.