VBA to match two separate column values from single row and update values

[truebluewoman]

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

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!

 

[Lukasier]

Should it always find correct values in range2? It should not stuck in loop if its finding a match

 

[truebluewoman]

Should it always find correct values in range2? It should not stuck in loop if its finding a match

It should. Every account is in the bottom area and each account has a line item #.

 

[Lukasier]

Okey, it seems like we can't get anywhere XD How about I will just write code that copy/paste values based on 2 separates conditons that works 100% and you will figure how to apply it in your VBA?

I am not working with intersect that often but I think somethings there is causing my code to crash

 

[truebluewoman]

Okey, it seems like we can't get anywhere XD How about I will just write code that copy/paste values based on 2 separates conditons that works 100% and you will figure how to apply it in your VBA?

I am not working with intersect that often but I think somethings there is causing my code to crash

I can work around that. Like I said, your help is very appreciated and I thank you again for your assistance so far. It's given me some ideas as well.

 

[Lukasier]

My reference in this code is active cell. You have to change that somehow to be working with your code.

Sub okey()

Dim Rel As Long
Dim Find1
Dim Find2
Dim i As Long


Rel = ActiveCell.Row

Find1 = Range("G" & Rel).Value
Find2 = Range("M" & Rel).Value

i = 71
Do Until Range("E" & i) = Find2 And Range("F" & i) = Find1
i = i + 1


Loop
Range("H" & i).Value = Range("N" & Rel).Value
Range("I" & i).Value = Range("S" & Rel).Value
Range("J" & i).Value = Range("R" & Rel).Value


End Sub

You need to add your sheet's name and change AcitveCell somehow to get what you want. Anyway, this VBA works. I checked on your data on the same ranges. Good luck!
Please let me know how it goes