Htykuut

I have two sheets:

Sheet 1 : consist of three columns (Status, Cost, Version)

Sheet 2 : Used as an Action Page with two columns (Formula, Version Number).

Question:

I want to set a version number in column two in sheet 2 and let the formula or the Button using VBA code to do the following:

1. The User set value = 5 in column 2 [Version Number] sheet 2


2. The System takes the value from [Version Number] cell
   


3. The System search for all rows containing the [Version Number] value = 5 , in sheet 1 column [Version]


4. The System Replace each cell in column [Status] having [Version] value= 5 , with Status Value = "Delivered".

Any help please either throw formula or using the VBA Code.

Regards

If that's all you want to achieve, and if you can use a formula in Sheet1, in the [Status] field, then probably all you want to do is to use the following formula:

=IF(C2 = 5, "Delivered", "Pending")

This is basically looking at your Version column and checks if your version number is 5, then change the text to Delivered, else use Pending (or whatever you need).

I would like to thank you all for your advice , after searching for the key code to know how to return the number of rows filled in a specific table , I found the solution and now I am able to do my logic. Below is my solution for the above question.

Steps:

1. Create Variable to hold the values


2. The below variable will hold the inserted value need to be updated


3. The below variable will hold the Item Version Column number in the table


4. The below variable will hold the value of each row under the column [Get_Version_Item_Column] to be used when comparing the values of each row


5. The below variable will hold the Item Progress Column number in the table


6. The below variable will hold the value of each row under the column [Get_Item_Progress_Column] to be used when comparing the values or each row


7. The below variable used to get the total number of used rows in the table


8. The below variable used as a counter to go throw a loop for all the rows

Create a Function that will do the below in Order:

9. Get Column Number from table 1 Sheet1 where header name = [Fixed in Version]




10. Get Column Number from table 1 Sheet1 where header name = [Item Progress]




11. Select Sheet 2 to get the value inserted in the Cell B1




12. Get Inserted value from the cell located inside the Sheet2




13. Set Counter value = 1 to start the loop from the first row after the header in table 1 Sheet 1




14. Open while Loop and set a condition if counter <= Total number of rows do the below




15. Start moving row by row in the table using the Cells Method : Cells(counter, Get_Version_Item_Column) ' in the cells method we set the [Row]=> counter variable which starts with value = 1 , [Column]=> Get_Version_Item_Column '




16. Get the value of the second column we want to put into the condition with the same way shown in Step 7




17. Start setting the condition usinf IF Else




18. If Get_Fixed_In_Version_Value = Get_Version_Inserted_Value And Get_Item_Progress_Value = "Approved" Then 'IF CORRECT NUMBER ' DO something Example [Replace the value in this row , this column , this cell with value = "" ]




19. Else ' Do something Else




20. Add 1 to the counter




21. Close the While Loop

Code Solution:

Public Get_Version_Inserted_Value As String

Public Get_Version_Item_Column As Integer

Public Get_Fixed_In_Version_Value As String

Public Get_Item_Progress_Column As Integer

Public Get_Item_Progress_Value As String

Public QATotal_Items_Row As Integer

Public counter As Integer



Function Change_Version_Item_Progress() As String



Get_Version_Inserted_Value = ThisWorkbook.Worksheets("Sheet2").Range("B1").Value2



Get_Version_Item_Column = Application.Match("Fixed in Version", Sheets("Sheet1").Rows(1), 0)



Get_Item_Progress_Column = Application.Match("Item Progress", Sheets("Sheet1").Rows(1), 0)



Sheets(Sheet1).Select

QATotal_Items_Row = WorksheetFunction.CountA(Range("B:B")) - 1 

counter = 1

 While counter <= QATotal_Items_Row



    Get_Fixed_In_Version_Value = ThisWorkbook.Worksheets("Sheet1").Range("Table1").Cells(counter, Get_Version_Item_Column).Value2



    Get_Item_Progress_Value = ThisWorkbook.Worksheets("Sheet1").Range("Table1").Cells(counter, Get_Item_Progress_Column).Value2



    If Get_Fixed_In_Version_Value = Get_Version_Inserted_Value And Get_Item_Progress_Value = "Approved" Then 



        Get_Item_Progress_Value = ThisWorkbook.Worksheets("Sheet1").Range("Table1").Cells(counter, Get_Item_Progress_Column).Value = "Delivered"



    Else 

        Get_Item_Progress_Value = ThisWorkbook.Worksheets("Sheet1").Range("Table1").Cells(counter, Get_Item_Progress_Column).Value = Get_Item_Progress_Value



    End If

        counter = counter + 1 

    Wend



End Function

Hope it will help.

Regards