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: 51
  • Range 2 - Destination (Partial).PNG
    Range 2 - Destination (Partial).PNG
    14.6 KB · Views: 52
Sort by date Sort by votes
I tried placing the code at the end, but it still only completes the first actions.

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

    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 = True
     
End Sub
 
Upvote 0

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
I know you should not share this kind of data, but could you please share at least some fake data with us? I mean it would be much more easier
 
Upvote 0
This is going to sound stupid, but do you need me to attach a file? For some reason, I don't see a way of doing that. I see options to upload images, but not a different type of file. I can add a table with data. Is that what you would like to see?

Edited: I just noticed and downloaded the Add-In as soon as I posted this. I'll add that to my next message.
 
Upvote 0
well you could download xl2bb to share range of your data or if thats too much trouble just paste raw data in both ranges so i could check how your macro works and find out why my wont run
 
Upvote 0
I tried using the XL2BB but my file kept crashing.

I'll try pasting the sample information as a table.

Range 1

My people can edit the following columns of data

That editable range is called VAR_OPTIONS

Budget Options
# Budgeted
Budgeted Frequency

GHIJKLMNOPQRS
MultDescriptionStd Frequency AssumptionDriversBudget Options# BudgetedBudgeted Frequency
Period Amount​
6570Conference 1Corporate Billing
$500​
Annual
1​
Line Item 2Conference 1PeriodBudgeted1Jan-20
$500​
6570Conference 2Corporate Billing
$200​
Annual
9​
Line Item 3Conference 2PeriodBudgeted1Sep-20
$200​
6570Shop ReportsVendor Billing
$175​
MonthlyLine Item 4Shop ReportsPeriodBudgeted1Monthly
$175​
6580New Hire DocsCorporate Billing
$135​
VariableLine Item 21 New Hire(s) @ $135 per New Hire, QuarterlyNew HireBudgeted1Quarterly
$135​
6580Background ChecksCorporate Billing
$40​
VariableLine Item 31 Applicant(s) @ $40 per Applicant, QuarterlyApplicantBudgeted1Quarterly
$40​
6580Online PostingsCorporate Billing
$200​
VariableLine Item 41 Online Posting(s) @ $200 per Job Posting, QuarterlyJob PostingBudgeted1Quarterly
$200​

Range 2 - Contains the named PERSLINEITEMS which is column F.

Desired Results are underlined.

EFGHIJ
Line ItemDescription/AssumptionAmountFrequency
Line Item 165701Technology License
$25​
Monthly
Line Item 265702Conference 1$500Jan-20
Line Item 365703Conference 2$200Sep-20
Line Item 465704Shop Reports$175Monthly
Line Item 565705
Line Item 665706
Line Item 765707
Line Item 865708
Line Item 965709
Line Item 10657010
Totals
6573Leasing Commissions
MONTHLY SUMMARY
Line ItemDescription/AssumptionAmountFrequency
Line Item 1657311019 Commissions @ $5 each
Line Item 265732
Line Item 365733
Line Item 465734
Totals
6574Temporary Employee - Maintenance
MONTHLY SUMMARY
Line ItemDescription/AssumptionAmountFrequency
Line Item 165741
Line Item 265742
Line Item 365743
Line Item 465744
Totals
6575Temporary Employee - Leasing
MONTHLY SUMMARY
Line ItemDescription/AssumptionAmountFrequency
Line Item 165751
Line Item 265752
Line Item 365753
Line Item 465754
Totals
6580Recruiting and Reloc Expense
MONTHLY SUMMARY
Line ItemDescription/AssumptionAmountFrequency
Line Item 165801CA Recruiting Materials
$200​
Feb-20
Line Item 2658021 New Hire(s) @ $135 per New Hire, Quarterly$135Quarterly
Line Item 3658031 Applicant(s) @ $40 per Applicant, Quarterly$40Quarterly
Line Item 4658041 Online Posting(s) @ $200 per Job Posting, Quarterly$200Quarterly
Line Item 565805
Line Item 665806
Line Item 765807
Line Item 865808
Line Item 965809
Line Item 10658010
Totals
6590Professional Licensing
MONTHLY SUMMARY
Line ItemDescription/AssumptionAmountFrequency
Line Item 165901
Line Item 265902
Line Item 365903
Line Item 465904
Totals
 
Upvote 0
Okey, step by step.

You actually DON'T WANT TO DO ANYTHING when someone change data in columns P,Q,R?

That is what I am getting from:
VBA Code: 
If Intersect(Target, Range("VAR_OPTIONS")) Is Nothing Then Exit Sub

And if changes happen in any other column, that should turn on the macro, is that you desire? :D
 
Upvote 0
VBA Code: 
Sub Worksheet_Change(ByVal Target As Range)
 
If Intersect(Target, Range("VAR_OPTIONS")) Is Nothing Then Exit Sub
Dim i As Long
Row = Target.Row
Application.EnableEvents = False

Dim Row As Integer


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

i = 71

Do Until Sheets("3-Other Personnel Exp").Range("F" & i) = Sheets("3-Other Personnel Exp").Range("G" & Row) And Sheets("3-Other Personnel Exp").Range("M" & i) = Sheets("3-Other Personnel Exp").Range("E" & Row)
i = i + 1
Loop




Application.EnableEvents = True
Sheets("3-Other Personnel Exp").Range("H" & i).Value = Sheets("3-Other Personnel Exp").Range("N" & Row).Value
Sheets("3-Other Personnel Exp").Range("I" & i).Value = Sheets("3-Other Personnel Exp").Range("S" & Row).Value
Sheets("3-Other Personnel Exp").Range("J" & i).Value = Sheets("3-Other Personnel Exp").Range("R" & Row).Value
End Sub
 
Upvote 0
Lukasier said:
Corrected, sorry
VBA Code: 
Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("VAR_OPTIONS")) Is Nothing Then Exit Sub
Dim i As Long
Dim Row As Integer
Row = Target.Row
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

i = 71

Do Until Sheets("3-Other Personnel Exp").Range("F" & i) = Sheets("3-Other Personnel Exp").Range("G" & Row) And Sheets("3-Other Personnel Exp").Range("M" & i) = Sheets("3-Other Personnel Exp").Range("E" & Row)
i = i + 1
Loop




Application.EnableEvents = True
Sheets("3-Other Personnel Exp").Range("H" & i).Value = Sheets("3-Other Personnel Exp").Range("N" & Row).Value
Sheets("3-Other Personnel Exp").Range("I" & i).Value = Sheets("3-Other Personnel Exp").Range("S" & Row).Value
Sheets("3-Other Personnel Exp").Range("J" & i).Value = Sheets("3-Other Personnel Exp").Range("R" & Row).Value
End Sub
Click to expand...
 
Upvote 0
Lukasier said:
Okey, step by step.

You actually DON'T WANT TO DO ANYTHING when someone change data in columns P,Q,R?

That is what I am getting from:
VBA Code: 
If Intersect(Target, Range("VAR_OPTIONS")) Is Nothing Then Exit Sub

And if changes happen in any other column, that should turn on the macro, is that you desire? :D
Click to expand...

It's the opposite. If they change any value in P,Q, or R, it triggers the first set of actions. That part was working properly. Every time I edit anything in those columns, the correct actions occur.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Summing Values
Replies
3
Views
269
LazyBug
LazyBug
E
  • Question
Estimating Spreadsheet-Summing
Replies
1
Views
174
Peter_SSs
Peter_SSs
I
  • Question
Delete entire row based off cell value ignoring 2nd row
Replies
6
Views
145
ItalianPlatinum
I
T
  • Question
Pull data from multiple sheets that match varying criteria
Replies
0
Views
101
T1991
T
A
  • Question
Code to Add Data From UserForm In Row Above or Below Based on Criteria
Replies
1
Views
264
amoverton2
A

Forum statistics

Threads
1,215,771
Messages
6,126,799
Members
449,337
Latest member
BBV123

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