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

T

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


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!
 

Attachments

  • Range 1 - Source.PNG
    Range 1 - Source.PNG
    36.1 KB · Views: 50
  • Range 2 - Destination (Partial).PNG
    Range 2 - Destination (Partial).PNG
    14.6 KB · Views: 51

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
I am guessing you want to run this code only for row when you made the change.

VBA Code: 
Dim Cell As Range
Dim NewRange As Range

set NewRange = 'specify exactly column F in range2 so this loop below can work

For Each Cell In NewRange

    If Cell.Value = Range("G" & target.Row).Value Then
       If Cell.Offset(, -1).Value = Range("M" & target.Row).Value Then
          
            Cell.Offset(, 2).Value = Range("N" & target.Row).Value
            Cell.Offset(, 3).Value = Range("S" & target.Row).Value
            Cell.Offset(, 4).Value = Range("R" & target.Row).Value
      
       End If
    End If

Next Cell
 
Upvote 0
Somehow this additional code is not being "triggered". The first portion of my if/and statement is still working, but it does not continue with the remaining update.

I changed my range "PERSLINEITEMS" only be the cells in column F.

Here's the full code:

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
 
If Intersect(Target, Range("VAR_OPTIONS")) Is Nothing Then Exit Sub

    Dim Cell As Range
    Dim NewRange As Range

    Set NewRange = Sheets("3-Other Personnel Exp").Range("PERSLINEITEMS")
    
 
Application.EnableEvents = False
     
If Sheets("3-Other Personnel Exp").Range("K" & Target.Row).Value = "Annual" And Sheets("3-Other Personnel Exp").Range("P" & Target.Row).Value = "Budgeted" Then
   
   Sheets("3-Other Personnel Exp").Range("Q" & Target.Row).Value = 1
   Sheets("3-Other Personnel Exp").Range("R" & Target.Row).Formula = "=IF(RC[-7]="""","""",IF(RC[-7]=""Monthly"",""Monthly"",IFERROR(IF(RC[-7]=""Annual"",INDEX(MONTHLOOKUP,MATCH(RC[-6],'Lists-Assumptions'!R15C7:R26C7,0),1)),"""")))"
   Sheets("3-Other Personnel Exp").Range("Q" & Target.Row & ":R" & Target.Row).Locked = True
   
        For Each Cell In NewRange
    
        If Cell.Value = Range("G" & Target.Row).Value Then
            If Cell.Offset(, -1).Value = Range("M" & Target.Row).Value Then
        
                Cell.Offset(, 2).Value = Range("N" & Target.Row).Value
                Cell.Offset(, 3).Value = Range("S" & Target.Row).Value
                Cell.Offset(, 4).Value = Range("R" & Targe.Row).Value
        End If
    End If
    
Next Cell
  
ElseIf Sheets("3-Other Personnel Exp").Range("K" & Target.Row).Value = "Monthly" And Sheets("3-Other Personnel Exp").Range("P" & Target.Row).Value = "Budgeted" Then
    
   Sheets("3-Other Personnel Exp").Range("Q" & Target.Row).Value = 1
   Sheets("3-Other Personnel Exp").Range("R" & Target.Row).Value = "Monthly"
   Sheets("3-Other Personnel Exp").Range("Q" & Target.Row & ":R" & Target.Row).Locked = True
   
   
ElseIf Sheets("3-Other Personnel Exp").Range("K" & Target.Row).Value = "Variable" And Sheets("3-Other Personnel Exp").Range("P" & Target.Row).Value = "Budgeted" Then
   
   Sheets("3-Other Personnel Exp").Range("Q" & Target.Row & ":R" & Target.Row).Locked = False
   
ElseIf Sheets("3-Other Personnel Exp").Range("P" & Target.Row).Value = "Not Budgeted" Then
   
   Sheets("3-Other Personnel Exp").Range("Q" & Target.Row).Value = 0
   Sheets("3-Other Personnel Exp").Range("R" & Target.Row).ClearContents
   
Else
        
    Exit Sub
 
End If
     
Application.EnableEvents = True
     
End Sub
 
Upvote 0
Okey, I need to know what are the names of these 2 sheets with 2 different ranges and on which sheet the intersect is triggered?
 
Upvote 0
Lukasier said:
Okey, I need to know what are the names of these 2 sheets with 2 different ranges and on which sheet the intersect is triggered?
Click to expand...

It's all within the same sheet "3-Other Personnel Exp". I have two named ranges within that same sheet. The intersect is triggered on that sheet.

Range 1 (intersect trigger) is called VAR_OPTIONS which = '3-Other Personnel Exp'!$P$24:$R$30

Range 2 (the lookup array) is called PERSLINEITEMS which = '3-Other Personnel Exp'!$F$71:$F$145

But I have several actions that are already triggered.

1. Each selected option updates specific cells within that row, based on the selections within the VAR_OPTIONS range.
2. After those values update, THEN the values from P, R, and S have to go into the bottom area of the worksheet where they will be summed along with other values that are entered.

Again, thank you for your help. I think we are really close!
 
Upvote 0
Okey, I think I will manage to update your code for it to work and not lose any of it's functionalities first thing tomorrow morning! :D
 
Upvote 0
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
 
If Intersect(Target, Range("VAR_OPTIONS")) Is Nothing Then Exit Sub

    Dim Cell As Range
    Dim NewRange As Range

    Set NewRange = Sheets("3-Other Personnel Exp").Range("F71:F145")
    
        For Each Cell In NewRange
    
        If Cell.Value = Range("G" & Target.Row).Value Then
            If Cell.Offset(, -1).Value = Range("M" & Target.Row).Value Then
        
                Cell.Offset(, 2).Value = Range("N" & Target.Row).Value
                Cell.Offset(, 3).Value = Range("S" & Target.Row).Value
                Cell.Offset(, 4).Value = Range("R" & Targe.Row).Value
        End If
    End If
    
Next Cell
 
Application.EnableEvents = False
     
If Sheets("3-Other Personnel Exp").Range("K" & Target.Row).Value = "Annual" And Sheets("3-Other Personnel Exp").Range("P" & Target.Row).Value = "Budgeted" Then
   
   Sheets("3-Other Personnel Exp").Range("Q" & Target.Row).Value = 1
   Sheets("3-Other Personnel Exp").Range("R" & Target.Row).Formula = "=IF(RC[-7]="""","""",IF(RC[-7]=""Monthly"",""Monthly"",IFERROR(IF(RC[-7]=""Annual"",INDEX(MONTHLOOKUP,MATCH(RC[-6],'Lists-Assumptions'!R15C7:R26C7,0),1)),"""")))"
   Sheets("3-Other Personnel Exp").Range("Q" & Target.Row & ":R" & Target.Row).Locked = True
   

  
ElseIf Sheets("3-Other Personnel Exp").Range("K" & Target.Row).Value = "Monthly" And Sheets("3-Other Personnel Exp").Range("P" & Target.Row).Value = "Budgeted" Then
    
   Sheets("3-Other Personnel Exp").Range("Q" & Target.Row).Value = 1
   Sheets("3-Other Personnel Exp").Range("R" & Target.Row).Value = "Monthly"
   Sheets("3-Other Personnel Exp").Range("Q" & Target.Row & ":R" & Target.Row).Locked = True
   
   
ElseIf Sheets("3-Other Personnel Exp").Range("K" & Target.Row).Value = "Variable" And Sheets("3-Other Personnel Exp").Range("P" & Target.Row).Value = "Budgeted" Then
   
   Sheets("3-Other Personnel Exp").Range("Q" & Target.Row & ":R" & Target.Row).Locked = False
   
ElseIf Sheets("3-Other Personnel Exp").Range("P" & Target.Row).Value = "Not Budgeted" Then
   
   Sheets("3-Other Personnel Exp").Range("Q" & Target.Row).Value = 0
   Sheets("3-Other Personnel Exp").Range("R" & Target.Row).ClearContents
   
Else
        
    Exit Sub
 
End If
     
Application.EnableEvents = True
     
End Sub
 
Upvote 0
I am 100% sure this code works fine, but I am not sure if we understand each other correctly. This code only looking for a match withing the row you made the change. That what you wrote earlier.
truebluewoman said:
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).
Click to expand...
So if you would make a change in range 1 for line 3, it would only match (or correct) the data in range 3 for this particular line 3. Is that what you want?
 
Upvote 0
Let me see if I can explain this better. The worksheet is a portion of a budget template for the various properties we manage.

Some expenses, if selected, must be budgeted within a specific month and are a set amount. Others they can budget for with varying intervals and amounts. Because some of our entities are on differing fiscal periods, I have some formulas that automatically place the costs in the correct budgeting periods.

The managers go through these expenses and choose to budget or not budget for them.

Example:
We have a Manager's conference that is listed at a certain rate. If they choose to budget to send their manager to the conference, they choose "Budgeted" (column "P" and then first trigger will read some information in various hidden columns that are pre-filled.

The first triggers reads that it's an "Annual" expense and that it's "Budgeted" (the FIRST two conditions), it automatically drops the formula that puts that into the proper January budgeting period. I wrote this code for the various possibilities. This code works the way I need it to for step 1. It locks and unlocks the appropriate cells and enters formulas where needed.

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
 
If Intersect(Target, Range("VAR_OPTIONS")) Is Nothing Then Exit Sub

Application.EnableEvents = False

If Sheets("3-Other Personnel Exp").Range("K" & Target.row).Value = "Annual" And Sheets("3-Other Personnel Exp").Range("P" & Target.row).Value = "Budgeted" Then
   
   Sheets("3-Other Personnel Exp").Range("Q" & Target.row).Value = 1
   Sheets("3-Other Personnel Exp").Range("R" & Target.row).Formula = "=IF(RC[-7]="""","""",IF(RC[-7]=""Monthly"",""Monthly"",IFERROR(IF(RC[-7]=""Annual"",INDEX(MONTHLOOKUP,MATCH(RC[-6],'Lists-Assumptions'!R15C7:R26C7,0),1)),"""")))"
   Sheets("3-Other Personnel Exp").Range("Q" & Target.row & ":R" & Target.row).Locked = True
        
       
ElseIf Sheets("3-Other Personnel Exp").Range("K" & Target.row).Value = "Monthly" And Sheets("3-Other Personnel Exp").Range("P" & Target.row).Value = "Budgeted" Then
    
   Sheets("3-Other Personnel Exp").Range("Q" & Target.row).Value = 1
   Sheets("3-Other Personnel Exp").Range("R" & Target.row).Value = "Monthly"
   Sheets("3-Other Personnel Exp").Range("Q" & Target.row & ":R" & Target.row).Locked = True
   
    
ElseIf Sheets("3-Other Personnel Exp").Range("K" & Target.row).Value = "Variable" And Sheets("3-Other Personnel Exp").Range("P" & Target.row).Value = "Budgeted" Then
   
   Sheets("3-Other Personnel Exp").Range("Q" & Target.row & ":R" & Target.row).Locked = False
   
    
ElseIf Sheets("3-Other Personnel Exp").Range("P" & Target.row).Value = "Not Budgeted" Then
   
   Sheets("3-Other Personnel Exp").Range("Q" & Target.row).Value = 0
   Sheets("3-Other Personnel Exp").Range("R" & Target.row).ClearContents

Else
    Exit Sub
 
End If
     
Application.EnableEvents = True
     
End Sub

The piece that is missing is, basically a second set of actions to take the values updated by this first code within the target rows and copies them into the bottom section based on that row matching the GL Account and Line Item Number.

I'm sorry if I am giving too much information, but I hope this explains it better!
 
Upvote 0
Okey, i think i get it now. At first try just change placement of code. Put my part at the end after all is done. The code i posted today
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Summing Values
Replies
3
Views
206
LazyBug
LazyBug
E
  • Question
Estimating Spreadsheet-Summing
Replies
1
Views
131
Peter_SSs
Peter_SSs
A
  • Question
Code to Add Data From UserForm In Row Above or Below Based on Criteria
Replies
1
Views
202
amoverton2
A
S
  • Question
Copy a column value when the values match to multiple sheets of the workbook
Replies
1
Views
230
sofas
S
B
  • Question
Matching Multiple Header Row Cell Values - Copy the data below to next available row.
Replies
1
Views
195
bobaol
B

Forum statistics

Threads
1,214,653
Messages
6,120,750
Members
448,989
Latest member
mariah3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top