How to tell macro to execute code on specific row?

Question

Previously I have created an Excel sheet which obtains data from a master table and perform Index & Match formula on selected columns based on what was keyed in the first column.

The code I came up with was:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("a4:a9999")) Is Nothing Then

 With Me.Range("b4:b9999")
    .Formula = "=IF(ISNA(INDEX(MasterEntry,MATCH(A4,table[Project No],FALSE),2)),"""",INDEX(MasterEntry,MATCH(A4,table[Project No],FALSE),2))"
    .Value = .Value
 End With

 With Me.Range("c4:c9999")
    .Formula = "=IF(ISNA(INDEX(MasterEntry,MATCH(A4,table[Project No],FALSE),3)),"""",INDEX(MasterEntry,MATCH(A4,table[Project No],FALSE),3))"
    .Value = .Value
 End With
End If

End Sub

How it works is when the first column detects any changes within the range a4 to a9999, it will change all the values in column b and c based on the range as well.

Currently I'm trying to modify it so that data will only change based on which row I select. For example, if I change the data in A5 only, I want B5 and C5 only to perform the code and the others remain unchanged.

Is it possible to use worksheet_change to perform this or do I need another approach entirely?

As an extension to the question, what happens if I want the same function to work in a table? The code used for table version is such:

Private Sub worksheet_change(ByVal target As Range)

If Not Intersect(target, Me.ListObjects("ProjectEntry").ListColumns("Asset No").DataBodyRange) Is Nothing Then

 With Me.Range("ProjectEntry[Description]")
    .Formula = "=IF(ISNA(INDEX(DieMaster,MATCH(B4,DieMaster[Asset No],FALSE),2)),"""",INDEX(DieMaster,MATCH(B4,DieMaster[Asset No],FALSE),2))"
    .Value = .Value
 End With

 With Me.Range("ProjectEntry[Preventive Stroke]")
    .Formula = "=IF(ISNA(INDEX(DieMaster,MATCH(B4,DieMaster[Asset No],FALSE),3)),"""",INDEX(DieMaster,MATCH(B4,DieMaster[Asset No],FALSE),3))"
    .Value = .Value
 End With

End If

End Sub

How would I code in the function if range is a table?

YowE3K · Accepted Answer · 2017-09-18 03:29:09Z

1

Assuming you are only changing one cell at a time, the change is reasonably simple - use Target to specify what to change:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("a4:a9999")) Is Nothing Then

 With Target.Offset(0, 1)
    .FormulaR1C1 = "=IF(ISNA(INDEX(MasterEntry,MATCH(RC1,table[Project No],FALSE),2)),"""",INDEX(MasterEntry,MATCH(RC1,table[Project No],FALSE),2))"
    .Value = .Value
 End With

 With Target.Offset(0, 2)
    .FormulaR1C1 = "=IF(ISNA(INDEX(MasterEntry,MATCH(RC1,table[Project No],FALSE),3)),"""",INDEX(MasterEntry,MATCH(RC1,table[Project No],FALSE),3))"
    .Value = .Value
 End With
End If

End Sub

I changed the code to use R1C1 notation when specifying the formulas, as I find that easier to read in this sort of situation. For example, in A1 notation you would need to use a formula in column B of

    .Formula = "=IF(ISNA(INDEX(MasterEntry,MATCH(A" & Target.Row & ",table[Project No],FALSE),2)),"""",INDEX(MasterEntry,MATCH(A" & Target.Row & ",table[Project No],FALSE),2))"

If you wish to clear cells in columns B & C when the cell in column A is cleared, just add an If statement:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("a4:a9999")) Is Nothing Then
        If IsEmpty(Target.Value) Then
            'Clear column B and C if column A is empty
            Target.Offset(0, 1).Resize(1, 2).Clear
        Else

            With Target.Offset(0, 1)
                .FormulaR1C1 = "=IF(ISNA(INDEX(MasterEntry,MATCH(RC1,table[Project No],FALSE),2)),"""",INDEX(MasterEntry,MATCH(RC1,table[Project No],FALSE),2))"
                .Value = .Value
            End With

            With Target.Offset(0, 2)
                .FormulaR1C1 = "=IF(ISNA(INDEX(MasterEntry,MATCH(RC1,table[Project No],FALSE),3)),"""",INDEX(MasterEntry,MATCH(RC1,table[Project No],FALSE),3))"
                .Value = .Value
            End With
        End If
    End If

End Sub

edited Sep 18, 2017 at 3:29

answered Sep 7, 2017 at 3:08

YowE3K

24k7 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

hjh93 Over a year ago

I tried the code you gave me and it works. However I seem to lost a function from my old code, that is when I delete the content from cell A the content in cell B and C will also delete as well. Now when I delete the content of a cell in column A, the others remain. Granted its not that big of a deal (I can always manually delete it) but is there a way to tell it to do it automatically?

hjh93 Over a year ago

I tried it and doesn't seem to be working... I did do some modifications to the code. In particularly If Not Intersect(target, Range("a4", Range("a" & Rows.Count).End(xlUp))) Is Nothing Then where I change it so it refers to everything instead of needing to change range every time limit reached. Also I switched the formula to IFERROR. It can still fill in no problem but cannot remove as your code suggested. Is it possible that these changes are affecting the code?

hjh93 Over a year ago

Also I should mention that column A uses drop down menu to select data. And I simply click the cell I want to delete and click Backspace.

YowE3K Over a year ago

@hjh93 Changing it to only execute the code if it is within the "used" part of column A should work, providing you don't try to clear the last row in column A (because, by the time the code gets to run, that cell is no longer in the "used" area). You can get around that by just using If Target.Row > 3 And Not Intersect(Target, Columns("A")) Is Nothing Then.

robinCTS · Accepted Answer · 2017-09-13 17:46:58Z

In answer to the second part of the question, the following code shows how to only update the edited row for a table.

Note that I've changed from inserting formulae into the sheet, to doing the calculations in VBA and inserting the values into the sheet:

Private Sub Worksheet_Change(ByVal Target As Range)

  If Intersect(Target, Me.Range("ProjectEntry[Asset No]")) Is Nothing Then Exit Sub

  Dim Ä As Excel.Application: Set Ä = Excel.Application
  Dim varMatchRowNum As Variant
  Dim varValue As Variant

  varMatchRowNum = Ä.Match(Target.Value2, Ä.Range("DieMaster[Asset No]"), 0)
  With Ä.Range("DieMaster").ListObject.ListColumns
    varValue = Ä.Index(.Item(2).DataBodyRange, varMatchRowNum)
    Me.Range("ProjectEntry[Description]").EntireColumn.Cells(Target.Row).Value _
    = IIf(IsError(varValue), vbNullString, varValue)

    varValue = Ä.Index(.Item(3).DataBodyRange, varMatchRowNum)
    Me.Range("ProjectEntry[Preventive Stroke]").EntireColumn.Cells(Target.Row).Value _
    = IIf(IsError(varValue), vbNullString, varValue)
  End With

End Sub

Note the usage of Application. instead of WorksheetFunction. to access the worksheet functions. This, coupled with the use of Variant type variables, allows us to trap the error that occurs if the match fails.

Collectives™ on Stack Overflow

How to tell macro to execute code on specific row?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related