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:
- Create Variable to hold the values
- The below variable will hold the inserted value need to be updated
- The below variable will hold the Item Version Column number in the table
- 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
- The below variable will hold the Item Progress Column number in the table
- 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
- The below variable used to get the total number of used rows in the table
- 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:
Get Column Number from table 1 Sheet1 where header name = [Fixed in Version]
Get Column Number from table 1 Sheet1 where header name = [Item Progress]
Select Sheet 2 to get the value inserted in the Cell B1
Get Inserted value from the cell located inside the Sheet2
Set Counter value = 1 to start the loop from the first row after the header in table 1 Sheet 1
Open while Loop and set a condition if counter <= Total number of rows do the below
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 '
Get the value of the second column we want to put into the condition with the same way shown in Step 7
Start setting the condition usinf IF Else
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 = "" ]
Else ' Do something Else
Add 1 to the counter
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