How to set value in a cell based on another cell value from another column using formula or VBA?












0















I have two sheets:enter image description here



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










share|improve this question

























  • You will need VBA to achieve this. But please note that because this is no free code writing service it is necessary to show either what you have tried so far and where you got stuck or errors (by showing your code) or at least to show what you have researched and the effort you made. Otherwise it is just asking us to do all the work for you.

    – Pᴇʜ
    Nov 21 '18 at 10:29
















0















I have two sheets:enter image description here



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










share|improve this question

























  • You will need VBA to achieve this. But please note that because this is no free code writing service it is necessary to show either what you have tried so far and where you got stuck or errors (by showing your code) or at least to show what you have researched and the effort you made. Otherwise it is just asking us to do all the work for you.

    – Pᴇʜ
    Nov 21 '18 at 10:29














0












0








0


1






I have two sheets:enter image description here



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










share|improve this question
















I have two sheets:enter image description here



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







excel vba formula






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 21 '18 at 13:17







SDiab

















asked Nov 21 '18 at 10:22









SDiabSDiab

265




265













  • You will need VBA to achieve this. But please note that because this is no free code writing service it is necessary to show either what you have tried so far and where you got stuck or errors (by showing your code) or at least to show what you have researched and the effort you made. Otherwise it is just asking us to do all the work for you.

    – Pᴇʜ
    Nov 21 '18 at 10:29



















  • You will need VBA to achieve this. But please note that because this is no free code writing service it is necessary to show either what you have tried so far and where you got stuck or errors (by showing your code) or at least to show what you have researched and the effort you made. Otherwise it is just asking us to do all the work for you.

    – Pᴇʜ
    Nov 21 '18 at 10:29

















You will need VBA to achieve this. But please note that because this is no free code writing service it is necessary to show either what you have tried so far and where you got stuck or errors (by showing your code) or at least to show what you have researched and the effort you made. Otherwise it is just asking us to do all the work for you.

– Pᴇʜ
Nov 21 '18 at 10:29





You will need VBA to achieve this. But please note that because this is no free code writing service it is necessary to show either what you have tried so far and where you got stuck or errors (by showing your code) or at least to show what you have researched and the effort you made. Otherwise it is just asking us to do all the work for you.

– Pᴇʜ
Nov 21 '18 at 10:29












2 Answers
2






active

oldest

votes


















2














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).






share|improve this answer
























  • Thank you DarXyde, for your help.while the solution you provided is to set a Formula inside the cell itself. But my question is different where each cell has a drop-down List so I don`t need to set a formula inside, I will attache an image for your reference it will describe my question more.

    – SDiab
    Nov 21 '18 at 13:12













  • Is a bit more clear now, and yes, you will need some VBA to help you with that. As @Pᴇʜ pointed out, unfortunately this requires a bit of work from your side too. All I can say, is very likely sheet events will help, and this might give you a starter on how to use those: excel-pratique.com/en/vba/worksheet_events.php

    – DarXyde
    Nov 21 '18 at 13:33













  • I suggest to use a button instead of events. This is what users are familiar with and it's easier to achieve.

    – Pᴇʜ
    Nov 21 '18 at 13:46



















0














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:




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


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


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


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


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


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


  7. 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 '


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


  9. Start setting the condition usinf IF Else


  10. 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 = "" ]


  11. Else ' Do something Else


  12. Add 1 to the counter


  13. 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






share|improve this answer


























  • Thank you Patrick for the format , I always face problem in customizing the code into this format . lol hhh

    – SDiab
    Nov 23 '18 at 9:57











Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53409926%2fhow-to-set-value-in-a-cell-based-on-another-cell-value-from-another-column-using%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























2 Answers
2






active

oldest

votes








2 Answers
2






active

oldest

votes









active

oldest

votes






active

oldest

votes









2














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).






share|improve this answer
























  • Thank you DarXyde, for your help.while the solution you provided is to set a Formula inside the cell itself. But my question is different where each cell has a drop-down List so I don`t need to set a formula inside, I will attache an image for your reference it will describe my question more.

    – SDiab
    Nov 21 '18 at 13:12













  • Is a bit more clear now, and yes, you will need some VBA to help you with that. As @Pᴇʜ pointed out, unfortunately this requires a bit of work from your side too. All I can say, is very likely sheet events will help, and this might give you a starter on how to use those: excel-pratique.com/en/vba/worksheet_events.php

    – DarXyde
    Nov 21 '18 at 13:33













  • I suggest to use a button instead of events. This is what users are familiar with and it's easier to achieve.

    – Pᴇʜ
    Nov 21 '18 at 13:46
















2














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).






share|improve this answer
























  • Thank you DarXyde, for your help.while the solution you provided is to set a Formula inside the cell itself. But my question is different where each cell has a drop-down List so I don`t need to set a formula inside, I will attache an image for your reference it will describe my question more.

    – SDiab
    Nov 21 '18 at 13:12













  • Is a bit more clear now, and yes, you will need some VBA to help you with that. As @Pᴇʜ pointed out, unfortunately this requires a bit of work from your side too. All I can say, is very likely sheet events will help, and this might give you a starter on how to use those: excel-pratique.com/en/vba/worksheet_events.php

    – DarXyde
    Nov 21 '18 at 13:33













  • I suggest to use a button instead of events. This is what users are familiar with and it's easier to achieve.

    – Pᴇʜ
    Nov 21 '18 at 13:46














2












2








2







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).






share|improve this answer













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).







share|improve this answer












share|improve this answer



share|improve this answer










answered Nov 21 '18 at 12:15









DarXydeDarXyde

26427




26427













  • Thank you DarXyde, for your help.while the solution you provided is to set a Formula inside the cell itself. But my question is different where each cell has a drop-down List so I don`t need to set a formula inside, I will attache an image for your reference it will describe my question more.

    – SDiab
    Nov 21 '18 at 13:12













  • Is a bit more clear now, and yes, you will need some VBA to help you with that. As @Pᴇʜ pointed out, unfortunately this requires a bit of work from your side too. All I can say, is very likely sheet events will help, and this might give you a starter on how to use those: excel-pratique.com/en/vba/worksheet_events.php

    – DarXyde
    Nov 21 '18 at 13:33













  • I suggest to use a button instead of events. This is what users are familiar with and it's easier to achieve.

    – Pᴇʜ
    Nov 21 '18 at 13:46



















  • Thank you DarXyde, for your help.while the solution you provided is to set a Formula inside the cell itself. But my question is different where each cell has a drop-down List so I don`t need to set a formula inside, I will attache an image for your reference it will describe my question more.

    – SDiab
    Nov 21 '18 at 13:12













  • Is a bit more clear now, and yes, you will need some VBA to help you with that. As @Pᴇʜ pointed out, unfortunately this requires a bit of work from your side too. All I can say, is very likely sheet events will help, and this might give you a starter on how to use those: excel-pratique.com/en/vba/worksheet_events.php

    – DarXyde
    Nov 21 '18 at 13:33













  • I suggest to use a button instead of events. This is what users are familiar with and it's easier to achieve.

    – Pᴇʜ
    Nov 21 '18 at 13:46

















Thank you DarXyde, for your help.while the solution you provided is to set a Formula inside the cell itself. But my question is different where each cell has a drop-down List so I don`t need to set a formula inside, I will attache an image for your reference it will describe my question more.

– SDiab
Nov 21 '18 at 13:12







Thank you DarXyde, for your help.while the solution you provided is to set a Formula inside the cell itself. But my question is different where each cell has a drop-down List so I don`t need to set a formula inside, I will attache an image for your reference it will describe my question more.

– SDiab
Nov 21 '18 at 13:12















Is a bit more clear now, and yes, you will need some VBA to help you with that. As @Pᴇʜ pointed out, unfortunately this requires a bit of work from your side too. All I can say, is very likely sheet events will help, and this might give you a starter on how to use those: excel-pratique.com/en/vba/worksheet_events.php

– DarXyde
Nov 21 '18 at 13:33







Is a bit more clear now, and yes, you will need some VBA to help you with that. As @Pᴇʜ pointed out, unfortunately this requires a bit of work from your side too. All I can say, is very likely sheet events will help, and this might give you a starter on how to use those: excel-pratique.com/en/vba/worksheet_events.php

– DarXyde
Nov 21 '18 at 13:33















I suggest to use a button instead of events. This is what users are familiar with and it's easier to achieve.

– Pᴇʜ
Nov 21 '18 at 13:46





I suggest to use a button instead of events. This is what users are familiar with and it's easier to achieve.

– Pᴇʜ
Nov 21 '18 at 13:46













0














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:




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


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


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


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


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


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


  7. 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 '


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


  9. Start setting the condition usinf IF Else


  10. 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 = "" ]


  11. Else ' Do something Else


  12. Add 1 to the counter


  13. 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






share|improve this answer


























  • Thank you Patrick for the format , I always face problem in customizing the code into this format . lol hhh

    – SDiab
    Nov 23 '18 at 9:57
















0














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:




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


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


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


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


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


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


  7. 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 '


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


  9. Start setting the condition usinf IF Else


  10. 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 = "" ]


  11. Else ' Do something Else


  12. Add 1 to the counter


  13. 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






share|improve this answer


























  • Thank you Patrick for the format , I always face problem in customizing the code into this format . lol hhh

    – SDiab
    Nov 23 '18 at 9:57














0












0








0







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:




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


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


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


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


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


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


  7. 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 '


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


  9. Start setting the condition usinf IF Else


  10. 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 = "" ]


  11. Else ' Do something Else


  12. Add 1 to the counter


  13. 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






share|improve this answer















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:




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


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


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


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


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


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


  7. 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 '


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


  9. Start setting the condition usinf IF Else


  10. 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 = "" ]


  11. Else ' Do something Else


  12. Add 1 to the counter


  13. 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







share|improve this answer














share|improve this answer



share|improve this answer








edited Nov 23 '18 at 9:29









Patrick Honorez

19k564119




19k564119










answered Nov 23 '18 at 9:26









SDiabSDiab

265




265













  • Thank you Patrick for the format , I always face problem in customizing the code into this format . lol hhh

    – SDiab
    Nov 23 '18 at 9:57



















  • Thank you Patrick for the format , I always face problem in customizing the code into this format . lol hhh

    – SDiab
    Nov 23 '18 at 9:57

















Thank you Patrick for the format , I always face problem in customizing the code into this format . lol hhh

– SDiab
Nov 23 '18 at 9:57





Thank you Patrick for the format , I always face problem in customizing the code into this format . lol hhh

– SDiab
Nov 23 '18 at 9:57


















draft saved

draft discarded




















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53409926%2fhow-to-set-value-in-a-cell-based-on-another-cell-value-from-another-column-using%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







這個網誌中的熱門文章

Xamarin.form Move up view when keyboard appear

Post-Redirect-Get with Spring WebFlux and Thymeleaf

Anylogic : not able to use stopDelay()