truebluewoman
New Member
- Joined
- Sep 26, 2014
- Messages
- 36
I am looking for a way to update the values in a separate row based on two values matching another rows' values.
1. There will only be a single match found.
2. Range 1 is the source
3. Range 2 is the destination and the lookup array
4. Range 1 and 2 are located on the same worksheet
In 1 row within Range 1, I have a value in column "G" and a different value in column "M". I have named the Range 2 area, but I will just use "Range 2" for ease.
In the lookup range, I want to find where the cell value in Range 1 Column "G" = the cell value in Range 2 column "F" and Range 1 Column "M" cell value = Range 2 Column "E" cell value
After the match is found, I need to set the values in that specific row in Range 2 so that
Range 2 "H" cell value = Range 1 "N" cell value
Range 2 "I" cell value = Range 1 "S" cell value
Range 2 "J" cell value = Range 1 "R" cell value
The reason I need that is because various options selected will generate different values in range 1, but the values in range 2 are static.
I have uploaded images of the two ranges so you can see the layout.
Example:
I want the values "National Managers Conference", "$2,500", "Jan-20" from Range 1 to populate Columns H, I, and J respectively on the row that has "Line Item 2" and "6570", because those match the values in columns "M" and "G" from Range 1.
I have done something similar but not on two matches in two separate ranges.
I would like to "trigger this" at each change of a value; which I have done with the "If Intersect(Target, Range("VAR_OPTIONS")) Is Nothing Then Exit Sub" command, but I can't seem to write the correct if/and statement conditions to match the target row's information (from Range 1) against the destination "Range 2" row's columns. I was thinking something like this (the brackets mean, I don't know how to write this).
Any help is appreciated. Thank you!
1. There will only be a single match found.
2. Range 1 is the source
3. Range 2 is the destination and the lookup array
4. Range 1 and 2 are located on the same worksheet
In 1 row within Range 1, I have a value in column "G" and a different value in column "M". I have named the Range 2 area, but I will just use "Range 2" for ease.
In the lookup range, I want to find where the cell value in Range 1 Column "G" = the cell value in Range 2 column "F" and Range 1 Column "M" cell value = Range 2 Column "E" cell value
After the match is found, I need to set the values in that specific row in Range 2 so that
Range 2 "H" cell value = Range 1 "N" cell value
Range 2 "I" cell value = Range 1 "S" cell value
Range 2 "J" cell value = Range 1 "R" cell value
The reason I need that is because various options selected will generate different values in range 1, but the values in range 2 are static.
I have uploaded images of the two ranges so you can see the layout.
Example:
I want the values "National Managers Conference", "$2,500", "Jan-20" from Range 1 to populate Columns H, I, and J respectively on the row that has "Line Item 2" and "6570", because those match the values in columns "M" and "G" from Range 1.
I have done something similar but not on two matches in two separate ranges.
I would like to "trigger this" at each change of a value; which I have done with the "If Intersect(Target, Range("VAR_OPTIONS")) Is Nothing Then Exit Sub" command, but I can't seem to write the correct if/and statement conditions to match the target row's information (from Range 1) against the destination "Range 2" row's columns. I was thinking something like this (the brackets mean, I don't know how to write this).
VBA Code:
If Sheets("3-Other Personnel Exp").Range("G" & Target.row).Value = [Column "F" in range 2] And Sheets("3-Other Personnel Exp").Range("M" & Target.row).Value = [Column "E" in range 2] Then
[Column "H".for range 2 matched row].Value = Sheets("3-Other Personnel Exp").Range("N"&Target.row).Value
[Column "I".for range 2 matched row].Value = Sheets("3-Other Personnel Exp").Range("S"&Target.row).Value
[Column "J".for range 2 matched row].Value = Sheets("3-Other Personnel Exp").Range("R"&Target.row).Value
Any help is appreciated. Thank you!