Change the status of Unique name by qualifying condition

[sholovka]

Hi,

This is a little convoluted and a little difficult to explain but I’ll try anyway.

What I am trying to do is:

I have a list of unique names in a table that have already been selected from another table on another sheet.
What happened there is that the selection that went to the New table included a status change in the First table to recognise that those unique names are now in the new table. (Status change was from ‘Available’ to ‘Allocated’)
This has worked OK, But
What I also need to do is, if the user recognises that some of the unique names selected should not have been selected at this point of time, that they can be removed from the New table and the status of that unique name is changed back to it's original status 'Available' in the First table.

What I thought was that in the New table, create a column with the number of rows equal to the table length which will always remain constant will have a validation list with lets say 'Yes' included in that list. So for each unique name to be returned the user would select ‘Yes’ in the validation list in the same row as the unique name that is to be removed.

Now this is the bit I haven’t been able to work out yet, how do I write something that will I suppose run look to see if that cell in the first determined row is = to ‘Yes’ then go and look at the First table find the unique name associated to the row where the ‘Yes’ was recognised and go and change the status (which will be in the 3rd column the first being the unique name). Then loop and look at the next cell run until it finds a blank then stop. (Note I thought that I would sort by that column to have all the ‘Yes’’ at the top of the table). Also somewhere in-between or at the end remove the unique names from the New Table (the last bit I'm pretty sure I know how to do).

Any assistance would be appreciated

Thanks

Sholovka

 

[sholovka]

Maybe if I rephrase the question.

I need to automate a procedure, which will:

In an Array, which I will call ‘New’, I have in the first column ‘Unique Names’.
In another Array on another sheet called 'Old' I also have ‘Unique Names’, which the array 'New' will only have ‘Unique Names’ obtained from.

If the first row of data looked at in array 'New' has the text 'Yes' selected in column 'F'
Then
Look at column 'H' (which will have the row number of the unique name in array 'Old', this is done via a embedded Match formula.)

I need then to look at array 'Old' select column 'C' and determine the row number via the value represented in column 'H' of the 'New' array. And change the text in the 'Old' array accordingly.

Then I need to loop this by looking at the next row in 'New' until ‘Yes’ is no longer recognised and then End Sub.

Any assistance will be appreciated as I am still trying to get this to work.

 

[tactps]

I am very confused by your descriptions. Please post the worksheets using Colo's HTML Maker (bottom of the page).

 

[sholovka]

Thanks for responding tactps, sorry for the abstract descriptions.

As much as I would like to give examples of the worksheets, there is a certain elements of confidentiality associated with the sheets, so I do not feel comfortable showing this. However I will try to provide as much detail as I can.

This is a disposal's module by various types of sales. The sale I am working on at the moment is an "Auction Template".
I have one sheet, which holds all the assets available for sale the “Assets Available for Sale” sheet and another template, which is the “Reserves” sheet that is directly linked to an “Auction Template”.

The sheet, which holds all the assets that are available for sale, will have a cell in each row, which will be a status selection. All assets go in as ‘Available for Sale’ the user will select which assets that will go for sale in a particular auction and change the status to ‘Allocate to Auction’. A macro runs copies all the asset ids associated to the status type and pastes them to the “Reserves” sheet then changes the status to ‘Allocated to Auction’ on the “Assets Available for Sale” sheet.

This all works OK. My problem is if the user has selected the assets to go to the auction then decides that some of the assets are not going to be sold. There is a need to be able to remove the recognised asset ids from the “Reserves” sheet and change the status in the “Assets Available for Sale” sheet back to the original status of 'Available for Sale'.

What I have done is to add 2 columns in the reserves sheet,
1) For each asset that is going to be removed from the “Reserves” sheet, selecting ‘Yes’ from the dropdown provided in this column does this. (Note asset information goes from left to right so any selection is representative of the row in question)
2) The next column has a lookup between the asset id in that row on the “Reserves” sheet with the asset id in the “Assets Available for Sale” sheet and brings back the row number associated to the asset id in the “Assets Available for Sale” sheet.

These would be the keys for my script.
If in the first determined row on the “Reserves” sheet that is looked at, contains ‘Yes’ (as per 1)) in the pre-determined column then it looks at the next column in the same row to determine the row number for the “Assets Available for Sales” sheet (as per 2)) then the script goes to that row number in the “Assets Available for Sales” sheet, since the column is not a variable and is already pre-determined, this should indicate which cell needs to be changed and does so accordingly.
Then I need to loop this script till it doesn’t find another ‘Yes’

Currently I believe that I have the right idea but I’m not certain how to write it.

 

[tactps]

I think this should give you what you need (although you will have to adapt to the correct columns):

Sub Macro3()
'
Worksheets(2).Range("D1").Select 'Assumes the Reserve Sheet is sheet #2

Do While ActiveCell.Text <> "" 'Stops the loop when empty cell found
flag1 = ActiveCell.Text
On Error GoTo finish 'Stops the macro if there's a problem
Rownumber = ActiveCell.Offset(0, 1).Value 'Used to pick the row
If flag1 = "Yes" Then
Worksheets(1).Select
Range("F" & Rownumber).Select
ActiveCell.Formula = "Allocate to auction"
Worksheets(2).Select
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If

Loop

finish:
End Sub

 

[sholovka]

Thanks tactps,

The script was what I was looking for. It was a lot easier than I expected.

Just a note, the loop didn't seem to work for me as it just went back to the originally active cell. So I made the row number a variable at the beginning of the script and looped it by adding one each time as per the extracted script below:

Dim intCellNumber As Integer
intCellNumber = 6

Do Until Sheets("Reserves").Range("D" & intCellNumber) <> "Yes"
intCellNumber = intCellNumber + 1

Sheets("Input - Reserves").Range("P" & intCellNumber - 1).Select


Thanks again

sholovka

 

[tactps]

Good work! Code often needs tweeking when adapted from one user to another.

Glad to help